실습 강좌 1 - 텍스트 데이터 파일을 테이블로 로딩하기



WebLogLoading_1강.dtsx

ex070217.zip

제 1강 텍스트 데이터 파일을 테이블로 로딩하기

 

한대성 (admin@sqlleader.com)

에이디컨설팅 / 책임 컨설턴트

SQLLeader.com / 운영자

 

 

가장 간단한 형태의 작업이면서도, 앞으로 진행할 강좌에서 다룰 기능들을 구현할 수 있는 형태의 작업에 대해 한참을 고민하다가 SQLLeader.com 웹 로그 데이터를 이용하자라고 결론을 내렸습니다. 매일 발생되면서, 다양한 유형의 데이터도 있으면서, 가끔 오류 데이터도 포함되어 있는 형태이기 때문에 적절하다고 생각합니다.^^

 

본 강의에서 구현할 내용은 D:\SSIS\Data\ 라는 폴더에 있는 ex070217.log라는 웹 로그 데이터 파일을 DB의 테이블에 적재하는 패키지를 만드는 것입니다.

 

ex070217.log는 아래와 같이 단순한 IIS 웹 로그 데이터 파일입니다.

 

 

이 데이터 파일을 적재할 대상 테이블은 다음과 같은 형태입니다.

USE TEMPDB GO   IF EXISTS (SELECT * FROM sysobjects WHERE Type = 'U' AND Name = N'WebLogData')        DROP TABLE WebLogData GO   CREATE TABLE WebLogData (        Seq int identity(1,1) NOT NULL,        Date char(10) NOT NULL,        Time char(8) NOT NULL,        IP varchar(15) NOT NULL,        URI_Stem varchar(255) NULL,        Uri_Query varchar(255) NULL,        Status smallint NULL,        User_Agent varchar(200) NULL,        CONSTRAINT PK__WebLogData PRIMARY KEY(Seq) ) GO

 

반드시 TEMPDB를 이용할 필요는 없습니다. 환경에 맞게 다른 DB를 지정하셔서 테이블을 만드시기 바랍니다.

 

 

 

자, 이제 패키지를 만들어 봅시다.

(필요에 따라서는 중간중간 건너뛰셔도 됩니다.^^)

 

시작 -> 프로그램(P) -> Microsoft SQL Server 2005 -> SQL Server Business Intelligence Development Studio를 실행합니다.

 

일명 BIDS라고도 불리는 이 프로그램이 패키지를 만들기 위한 작업 툴입니다.

 

상단 메뉴에서 파일(F) -> 새로 만들기(N) -> 프로젝트(P)를 선택하고 프로젝트 설정 창에서 Integration Services 프로젝트를 선택합니다. 프로젝트 파일이 저장될 위치 등을 적절히 변경하도록 합니다.

 

SSIS 패키지를 만드는 데 솔루션은 뭐고 또 프로젝트는 뭐냐 라고 생각하실 수 있을 것 같아 간단히 설명 드리자면, SSIS 패키지는 Visual Studio 환경을 이용하여 개발합니다. 솔루션이나 프로젝트 등은 Visual Studio에서 어떠한 프로그램이나 패키지 등을 만들기 위한 개발 단위이며, SSIS 패키지를 만드는 행위 또한 이 규칙을 따라야 합니다. 하지만 최종적으로 패키지가 실행되거나 관리될 때에는 솔루션 또는 프로젝트 등은 사용되지 않고 오직 dtsx 확장자를 가진 패키지 파일만 이용됩니다.

 

조금 번거롭게 보일지도 모르겠지만 다음과 같이 좋게 생각해 볼 수도 있겠지요..

큰 회사에서 각 부서별로 여러 개의 데이터 처리 패키지를 만든다고 할 때, 아래와 같이 계층적으로 회사의 패키지들을 관리할 수도 있는 기능입니다.

 

“에이디컨설팅”이라는 회사 수준의 솔루션 내에 관리부, 이관부, 개발부라는 부서별 프로젝트가 존재하고, 프로젝트 내에 개별 패키지들이 존재하는 형태로 관리할 수 있겠지요. 여기서의 관리는 패키지 개발에 대한 관리를 말합니다. (소스 관리 형태이겠지요.)

 

 

일단 프로젝트가 만들어졌으면 다음과 같은 빈 패키지 화면이 덩그러니 나타날 것입니다.

 

우선 패키지 이름부터 변경합니다. 오른쪽의 솔루션 탐색기에서 Package.dtsx 패키지의 이름을 WebLogLoading.dtsx 라고 변경합시다.

 

왼쪽의 도구 상자에서 데이터 흐름 작업을 제어 흐름 영역에 추가하고, 이 작업의 이름을 『WebLog 데이터 로딩』으로 변경합니다.

 

 

※ 패키지 이름이나 작업 이름을 지정하는 것은 번거롭고 생산성을 떨어뜨리는 행위일 수도 있으나 예제를 만들 때부터 이러한 습관을 유지하실 것을 권고 드립니다.

 

 

WebLog 데이터 로딩 작업을 더블 클릭해서 데이터 흐름 영역을 엽니다.

 

왼쪽의 도구 상자가 제어 흐름에서와는 다른 요소들로 변경되었을 것입니다. 이 작업 개체들은 데이터 흐름과 관련된 개체들로 구성된 것입니다.

 

도구 상자의 데이터 흐름 원본에서 플랫 파일 원본을 데이터 흐름에 추가하고 이 원본 개체의 이름을 WebLogFile이라고 변경합니다.

 

 

플랫 파일 원본을 이용하여 텍스트 파일의 데이터를 읽어오겠다라는 것을 정의한 것이라 할 수 있습니다. 추가한 WebLogFile 원본을 더블 클릭해서 편집기를 띄웁니다.

 

SSIS의 연결 관리자에 플랫 파일 연결이 정의되어 있는 경우에는 원본 편집기에서 나타나겠지만, 아직 추가하지 않은 상태라 아무런 목록이 나타나지 않습니다. 오른쪽의 새로 만들기(N)를 클릭하여 플랫 파일 연결을 하나 정의합니다.

 

다음과 같이 웹 로그 예제 파일이 있는 경로를 지정하고 건너뛸 머리글 행을 4로 설정합니다.

 

열 탭을 클릭한 후, 열 구분 기호 부분에 스페이스(‘ ‘)를 입력한 후, 열 다시 설정을 선택하면 자동으로 열이 설정됩니다.

 

 

 

고급 탭에서 각 열에 대한 속성을 다음과 같이 지정합니다.

열 이름	Name	DataType	OutputColumnWidth
열 0	Date	문자열(DT_STR)	10
열 1	Time	문자열(DT_STR)	8
열 2	IP	문자열(DT_STR)	15
열 3	UserName	문자열(DT_STR)	1
열 4	Method	문자열(DT_STR)	4
열 5	Uri_Stem	문자열(DT_STR)	255
열 6	Uri_Query	문자열(DT_STR)	255
열 7	Status	부호 있는 2바이트 정수 [DT_I2]
열 8	User_Agent	문자열(DT_STR)	200
열 9	Referer	문자열(DT_STR)	255

 

 

이제 확인을 눌러 플랫 파일 연결 관리자 편집기를 닫고, 플랫 파일 원본 편집기에서 열 탭을 선택하여 사용할 열 정보를 확인합니다.

 

자, 이제 일단 파일로부터 읽어오는 기능은 구현이 되었습니다. 이제 대상 테이블을 설정하도록 하겠습니다.

도구 상자의 데이터 흐름 대상 부분에서 OLE DB 대상을 선택하여 데이터 흐름에 추가한 후, 이름을 WebLogTable로 변경하고, WebLogFile의 녹색 선을 끌어서 연결시킵니다.

 

 

WebLogTable을 더블 클릭하여 OLE DB 대상 편집기를 연 후, OLE DB 연결 관리자 설정 부분에서 새로 만들기(N)을 클릭하여 OLE DB 연결을 추가합니다. 이 역시, 이미 SSIS 패키지의 연결 관리자에서 OLE DB 연결을 지정해 놓았다면 드롭 다운 박스에 나타나겠지만, 미리 지정한 것이 없기에 대상에서 직접 추가하는 것입니다.

 

OLE DB 연결 관리자 구성 창에서 새로 만들기(E)를 이용하거나 사전에 정의해 놓은 DB 연결 설정이 있으면 해당 연결 정보를 선택합니다. 이 때의 서버 및 DB는 위의 예제 테이블이 있는 곳을 지정하는 것입니다.

 

연결을 설정하고 확인을 누른 후, OLE DB 대상 편집기에서 테이블을 지정합니다.

 

매핑 탭을 클릭하여 매핑이 제대로 이루어졌는지를 확인합니다. 기본적으로 동일한 이름에 대해 매핑을 자동으로 설정하게 됩니다.

 

 

자, 이제 간단한 예제 패키지가 완성되었습니다. 끝내기 전에 하나만 더 설정하도록 합시다.

연결 관리자 부분에 보면 새롭게 추가된 OLE DB 연결이 있을 것입니다. 이 연결의 이름을 WebLogTargetTable로 변경하도록 합니다.

 

패키지를 실행시켜 보면 다음과 같이 실행되며, 테이블도 한 번 확인해 보시기 바랍니다.