요약 리포트 생성 및 발송 패키지 구현 – (2) 엑셀에 출력하기

요약 리포트 생성 및 발송 패키지 구현 – (2) 데이터 읽어와서 엑셀에 출력하기   한대성 MS SQL Server MVP 에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자     업무 때문에 잠시 바빠서 강좌가 늦어졌습니다.^^ 이번 강좌에서는 데이터를 읽어와서 엑셀에 출력하는 기본 패키지를 만들도록 하겠습니다. 우선 간단하게 만든 다음, 조금씩 동적인 기능을 넣으면서 수정하도록 합시다.   자, 처음 접하는 분들을 위하여~ 시작 메뉴에서                   프로그램 à Microsoft SQL Server 2005 à SQL Server Business Intelligence Development Studio 를 엽니다.   파일(F) à 새로 만들기(N) à 프로젝트(P)를 선택한 후, Integration Service 프로젝트를 새로 하나 만듭니다.   참고) Integration Services 패키지, 간단히 SSIS 패키지에서 프로젝트는 아무런 역할을 하지 않습니다. 패키지를 만들 때 Visual Studio 개발 환경을 사용하기 때문에 Visual Studio의 기본 구조인 솔루션 및 프로젝트가 만들어집니다. 실제로 패키지를 실행시킬 때에는 패키지 개별 파일인 dtsx 파일만 사용됩니다.   오른쪽에 있는 솔루션 탐색기의 SSIS 패키지에서 새로운 패키지를 하나 추가한 후, 패키지 이름을 GenerateExcel.dtsx로 변경합니다.   화면의 하단에 있는 연결 관리자 부분에서 마우스 오른쪽 클릭을 해서 나타나는 메뉴 중, 새 OLE DB 연결(N)을 선택하여 OLE DB 연결을 추가합니다. 이 때 속성은, 앞 강좌에서 준비한 데이터베이스로 지정합니다.     이제, 도구 상자에서 데이터 흐름 작업을 선택한 후, 제어 흐름 영역에 추가합니다.   추가한 데이터 흐름 작업을 더블 클릭하면 새로운 작업 영역이 열립니다. 이 영역이 데이터 흐름 영역이며, 방금 추가한 데이터 흐름 작업의 세부 작업 기능 구현 영역이라 생각하시면 됩니다.   자, 도구 상자를 확인해 봅시다. 데이터 흐름 영역의 도구 상자는 앞에서 본 제어 흐름 영역의 도구 상자와는 다르게 구성되어 있습니다.   이 중에서 OLE DB 원본을 선택합니다. 저장 프로시저로부터 데이터를 읽어오는 부분을 만드는 것입니다.   빨간 경고가 있다고 잘못되었다라고 생각하지 마시고, 추가한 OLE DB 원본을 더블 클릭한 다음, 아래와 같이 연결 관리자와 쿼리를 지정합니다.   뒤에서 SQL 명령 텍스트 부분은 변경할 것입니다. 우선 위와 같이 특정 일자를 지정한 후, 왼쪽에 있는 열 탭을 선택하여 출력 열이 제대로 나오는지 확인한 후, 편집기를 닫습니다.     이제, 도구 상자에서 Excel 대상을 선택한 후, 데이터 흐름 영역에 추가하고 OLE DB 원본의 녹색 연결선을 이용하여 연결시킵니다.     Excel 대상을 더블 클릭해서 편집기를 엽니다. Excel 대상 편집기를 보면 연결 관리자부터 약간 이상하네요. Excel 연결인데 웬 OLE DB 연결 관리자?! (오류입니다. 기능상 문제가 아니기 때문에 버그는 아니겠죠ㅎㅎ)   연결 관리자 부분의 새로 만들기(N)를 클릭해서 데이터를 저장할 엑셀 파일을 지정합니다. 파일이 아직 없더라도 이름만 지정해줘도 됩니다.   이제, Excel 시트의 이름(T) 부분 옆에 있는 새로 만들기(E)를 클릭해서 새로운 엑셀 테이블을 하나 만듭시다. 그냥 기본적으로 만들어지는 Excel 대상이라는 이름으로 하나의 시트를 만들었습니다. 그런 다음, 왼쪽의 매핑 탭을 선택해서 열 정보가 매핑되는지 확인하고 편집기를 닫습니다.   그런데~, 어라! 왜 빨간딱지가 계속 있는 것인가요? 마우스를 갖다 대니 친절히(?)도 원인인 듯한 메시지를 뿌려는 주는데, 대체 뭔 말인지, 어떻게 해야 할 지 모를 메시지입니다. 여기서부터 SSIS 패키지의 시련은 시작됩니다. (이전 버전인 DTS는 그냥 이런 단계로 진행하면 잘 되었었는데. ㅡ,.ㅡ)   원인을 짚고 넘어가자면, DB로부터 읽어오는 Date 열은 varchar 형인데, 저장되는 엑셀 파일의 데이터 유형은 nvarchar이기 때문입니다. 텍스트 유형이긴 하지만, 같은 유형은 아니라는 것이지요. 그럼 엑셀 테이블을 만들 때 varchar 형태로 만들면 되지 않을까요? 유감스럽게도 엑셀 파일의 텍스트 형은 모두 nvarchar, 정확히 말하면 유니코드 텍스트 형태만 가능합니다.   이러한 문제에 대한 방법은 두 가지 있습니다. a. 읽어올 때부터 그냥 varchar 대신 nvarchar로 읽어온다. – 간단하고도 속 편한 방법입니다. b. 중간에 유형을 바꿔주는 변환을 둔다. – 귀찮은 방법입니다.   본 강좌에서는 귀찮은 방법으로 가겠습니다.   OLE DB 원본과 Excel 대상 사이의 녹색선을 선택한 다음, Delete 키를 눌러서 지워버립니다. 그런 다음, 도구 상자에서 데이터 변환이라는 개체를 추가한 다음, OLE DB 원본의 녹색선과 연결합니다. 추가한 데이터 변환을 더블 클릭해서 편집기를 연 다음, 아래와 같이 간단한 작업을 합니다. 유니코드 형태로 열 유형을 변경하여 새로운 열을 하나 추가하는 작업입니다.   확인을 눌러 편집기를 닫은 다음, 데이터 변환의 녹색 선을 Excel 대상에 연결합니다. 그런 다음, Excel 대상을 열고, 변환 탭을 선택해서 아래와 같이 Data 열에 대한 매핑을 사본-Data 열로 변경합니다.     어쨌든 빨간 딱지는 없어졌네요. 실행해 봅시다.   엑셀 파일이 만들어지긴 했습니다.         [엑셀 파일 꾸미기]   엑셀 모양이 너무 투박하네요. 다음과 같이 약간 색깔 좀 넣어보고, 자릿수도 좀 지정해 봅시다.   그래도 투박하기는 마찬가지이지만, 조금 낫네요.   만약, 이렇게 저장해놓고 다시 패키지를 수행시키면 어떻게 될까요?   이와 같이 덧붙여집니다. 이건 원하는 모습이 아닌데. ㅡ,.ㅡ   매일 위와 같은 모양이 되도록 하기 위해서는 다음과 같은 작업이 필요합니다.   제어 흐름 영역에서 SQL 실행 작업을 두 개 추가하고 각각의 이름을 Excel 시트 삭제, Excel 시트 생성 이라고 보기 좋게 변경합시다. 그런 다음, 아래와 같이 각 작업을 연결합니다.   첫 번째 SQL 실행 작업(Excel 시트 삭제) 편집기를 연 다음, 아래와 같이 ConnectionType을 EXCEL로 변경하고 Connection을 Excel 연결 관리자로 지정한 다음, SQLStatement에 다음과 같은 쿼리를 입력합니다.   DROP TABLE `Excel 대상`   이 때, 주의할 것은 작은 따옴표 처럼 보이는 것은 작은 따음표가 아닌, 키보드의 왼쪽 상단에 있는 ~ 키 밑에 있는 따옴표입니다. (이름 뭐라카더라??)   비슷한 방법으로 Excel 시트 생성 작업에서는 아래와 같은 쿼리를 입력합니다. 여기서도 따옴표 주의   CREATE TABLE `Excel 대상` (     `Date` NVARCHAR(10),     `Cnt` INTEGER,     `Value1Avg` DOUBLE PRECISION,     `Value2Avg` DOUBLE PRECISION,     `Value3Avg` DOUBLE PRECISION )   꼭 두 작업을 분리해서 해야 하나요? 중간에 GO 두고선 그냥 하나로 처리 안되나요? 안됩니다.   자, 여러 번 돌려봅시다. 데이터가 아래로 붙지는 않습니다.     강좌가 생각보다 많이 길어졌네요.   여기서 중요한 사실 하나 밝힙니다. 64bit 장비에서는 엑셀 출력 및 입력 다 안됩니다. (두두두둑 – 돌 맞는 소리) 64bit에는 엑셀 원본 및 대상에서 사용하는 Jet Engine Provider가 없기 때문입니다. 그렇다고 강좌 포기하지 마시고, Excel 대상 대신, 텍스트 대상 등으로 바꿔서 해보세요.   그럼 다음 강좌에서 뵙지요~   @.@

한대성 작은 따옴표 반대 모양 (`) : 키보드의 1 키 왼쪽에 있는 놈 ::: Grave (그레이브) 라고 하네요. Back quote 라고도 하고, 한글말로는 강세표. ^_^ 2008/05/18 김도열 64bit 장비에서 엑셀 입출력이 포함된 SSIS 패키지를 실행하려면... 단계를 추가할 때, Type에서 Operation system (CmdExec)를 선택하고, Command란에 다음과 같은 구문을 적어주면 됩니다. "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "<SSIS 패키지 파일 경로>" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW