DB 데이터를 엑셀 시트에 분할하여 내보내기 (3)

- 작업 5. Create Table (SQL 실행 작업)

엑셀에 테이블을 생성하는 작업을 추가하겠습니다.

1.       SQL 실행 작업을 For 루프 컨테이너 안에 추가합니다. 이름은 Create Table이라고 변경하겠습니다.

2.       Sheet명 생성 및 Sheet Exists 체크와 연결해줍니다.

3.       Create Table 작업을 더블 클릭해서 편집 창을 엽니다.

4.       ConnectionType 속성을 Excel로 변경하고 Connection 속성 값으로 TargetExcel을 선택해주고 확인을 클릭하여 편집기를 닫습니다.

5.       Create Table의 Expressions 속성을 추가합니다. SqlStatementSource 속성에 아래와 같이 입력합니다.

"CREATE TABLE `" + @[사용자::SheetName]  + "` ( `Seq` INTEGER, `Data` NCHAR(7) )"

 

이미지를 가능하면 줄이려고 한번에 캡쳐를 했더니 뭔가 복잡하네요.

5번 단계의 작업을 화살표를 이용한 경로로 표시했는데 콩떡같이 말해도 찰떡같이 알아들어주시길… -_-;;;

 

6.       Sheet명 생성 및 Sheet Exists 체크와 Create Table을 연결한 작업 선을 더블 클릭해서 선행 제약 조건 편집기를 엽니다. 평가 작업을 식으로 선택하고 아래 식을 입력하는 창에
@FileExistsYN == True && @SheetExistsYN == False
를 입력합니다. 내용인즉 파일은 있지만 Sheet가 없으면 생성하라는 조건입니다. 그리고 아래 여러 제약 조건 그룹 박스 안에서 ‘논리적 OR, 한 제약 조건이 True가 되어야 합니다.’를 선택합니다. 여기서 논리적 AND를 선택하지 않은 이유는 다음 작업에서 Table을 Drop하는 작업을 추가할건데 그 작업을 분기로 태울 생각이기 때문입니다. AND와 OR의 차이가 궁금하신 분은 Drop Table 작업을 추가하고 OR을 AND로 바꿔서 한번 테스트 해보시기 바랍니다.

 

자~, 여기까지 작업했으면 디버그를 실행해서 패키지를 한번 실행시켜 보겠습니다.

…

제대로 작성이 됐다면 엑셀 파일에 Sheet1, 2, 3 옆에 주욱~ Data001, 002~017 까지 Sheet가 생성됐을 것입니다. 다시 한번 실행해볼까요? 조건을 입력했기 때문에 Create Table 앞까지만 실행이 되는 것을 확인하실 수 있을 것입니다.

 

- 작업 6. Drop Table (SQL 실행 작업)

동일한 테이블이 존재할 때 테이블을 제거하고 재생성을 할 것입니다. 그 작업을 위해 Drop Table 작업을 추가합니다.

1.       SQL 실행 작업을 For 루프 컨테이너 안에 추가하고 이름은 Drop Table로 변경하겠습니다.

2.       Sheet명 생성 및 Sheet Exists 체크 작업과 연결하고 다음 작업으로 Create Table 작업과 연결합니다. 즉 이렇게 연결합니다.

3.       더블 클릭하여 편집기를 열고 ConnectionType은 EXCEL로 변경하고 Connection은 TargetExcel로 선택합니다. 확인 버튼을 클릭하고 편집기를 닫습니다.

4.       Create Table에서 Expressions를 추가한 것과 동일한 작업을 하겠습니다. 단지 쿼리만 다른 쿼리를 입력합니다.
"DROP TABLE `" + @[사용자::SheetName]  + "`"
만약 이해가 안 되신다면 Create Table의 5번 작업을 참고해주세요.

5.       Sheet명 생성 및 Sheet Exists 체크 작업과 연결된 작업 선을 더블 클릭해서 선행 제약 조건 편집기를 엽니다. 평가 작업은 식을 선택하시고 식 입력 란에는
@FileExistsYN == True && @SheetExistsYN == True
를 입력합니다. 즉, 엑셀 파일이 있고 입력하려는 테이블이 벌써 존재하면 실행하라는 조건이 되겠습니다.

6.       그 상태에서 다시 한번 패키지를 디버깅으로 실행해볼까요?
어떤 가요? 예상한 대로 이번엔 Drop Table을 타고 Create Table을 실행합니다.

 

- 작업 7. 엑셀로 내보내기 (데이터 흐름 작업)

자, 이제 엑셀 파일은 데이터를 받을 준비가 다 되었습니다. DB에서 데이터를 읽어 엑셀에 내보내기만 하면 됩니다.

1.       데이터 흐름 작업을 For 루프 컨테이너에 추가하고 이름은 엑셀로 내보내기 라고 변경하겠습니다.

2.       엑셀로 내보내기의 속성 중에서 DealyValidation 값을 True로 변경합니다.

3.       Create Table과 연결해주고 엑셀로 내보내기를 더블 클릭해서 데이터 흐름 작업 디자인 모드로 전환합니다.

 

- 데이터 흐름 구성 요소 1. OLE DB 원본

1.       데이터 흐름 작업에 OLE DB 원본을 추가합니다.

2.       OLE DB 연결 관리자는 SourceDB를 선택하고 데이터 액세스 모드는 SQL 명령을 선택합니다. SQL 명령 텍스트에는 아래의 쿼리를 입력합니다.

SELECT TOP 60000 Seq, Data FROM SourceTable WITH (NOLOCK) WHERE Seq > ? Order by Seq

3.       우측의 매개 변수 버튼을 클릭해서 매개 변수 0에 변수 i를 설정해줍니다.

 

- 데이터 흐름 구성 요소 2. 데이터 변환

엑셀의 문자열 데이터 형식은 유니코드이기 때문에 데이터 형식을 변환 시켜줘야합니다.

1.       데이터 변환을 추가해서 OLE 원본과 연결시켜 줍니다.

2.       데이터 변환을 더블 클릭해서 편집기를 열고 Data 앞의 체크 박스를 선택해줍니다.

3.       데이터 형식을 유니코드 문자열로 변경합니다.

 

- 데이터 흐름 구성 요소 3. Excel 대상

1.       Excel 대상을 추가해서 데이터 변환과 연결시켜 줍니다.

2.       더블 클릭해서 편집기를 열고 Excel 시트의 이름 중에서 Data001 을 선택합니다.
만약에 시트에서 Data000 시리즈가 없다면 앞에서 Create Table을 실행하지 않은 것입니다.
실행해서 참조 Sheet를 생성해주세요.

3.       좌측 매핑 메뉴를 선택하고 Data -> Data 대신에 위에서 데이터 변환을 시켜준 열과 매핑을 시켜줍니다. 저의 경우에는 사본 – Data -> Data 입니다.

 

4.       Excel 대상의 속성 중에 AccessMode 값을 ‘변수를 사용한 OpenRowset’으로 변경합니다.
그리고, OpenRowsetVariable 속성의 값을 ‘사용자::SheetName’으로 설정합니다.

 

- 결과

자, 모든 작업이 완료되었습니다. 실행 한번 해 볼까요? 데이터가 백만 건이기 때문에 엑셀 파일로 가져오는데 다소 시간이 소요됩니다.

 

마지막에 40,002행이 입력된 건 테스트 하느라 중간에 제가 Insert를 두 번 더 해서 그렇습니다.

완료되니 11Kb하던 엑셀 파일이 100메가까지 성장했군요. 엑셀 파일의 이름 관리자 정보는 다음과 같습니다.

(그룰도 아닌 것이 17성장까지 했습니다;;;)