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

- 작업 1. TotalCount 조회 (SQL 실행 작업)
앞에서 원본 DB 데이터를 만들 때 만 건을 입력했습니다. 가져올 전체 데이터 건수가 백만 건이라는 것을 알고 있지만 다양한 활용을 위해 데이터 건 수를 조회하는 작업을 추가하겠습니다.

1.       패키지 범위의 변수를 추가합니다.

이름	데이터 형식	값
TotalCount	Int32	0

 

2.       SQL 실행 작업을 추가합니다. 이름을 TotalCount 조회로 변경하겠습니다.

3.       TotalCount 조회를 더블 클릭해서 SQL 실행 작업 편집기를 엽니다.

4.       Connection 속성에서 <새 연결…>을 선택해서 OLE DB 연결 관리자 구성을 엽니다.
앞에서 생성한 원본 데이터 DB에 연결하겠습니다.
원본 DB가 좌측 데이터 연결에 등록되어 있다면 선택하고 확인을 클릭하시고 없다면 새로 만들기를 클릭해서 연결 관리자를 열고 DB 접속 정보를 입력합니다.
확인 버튼을 클릭해서 SQL 실행 작업 편집기 창으로 돌아옵니다.

 

5.       SQLStatement 속성에 다음의 쿼리를 입력합니다.

SELECT ? = COUNT(*) FROM SourceTable WITH (NOLOCK)

 

여기까지 작업되었습니다.

 

6.       SQLStatement 속성에 ?로 입력된 매개 변수와 연결 작업을 합니다.
좌측 매개 변수 매핑 메뉴(?)를 선택합니다.
추가를 클릭해서 TotalCount 변수와 매핑 시켜줍니다.

 

7.       확인을 클릭하고 편집기를 닫습니다.

8.       하단에 있는 연결 관리자 창에 방금 DB와 연결한 연결 정보가 생성됐을 것입니다.
SourceDB 라고 이름을 변경해주겠습니다.

9.       화면 결과

 

- 작업 2. 엑셀 파일 경로 알아오기 (스크립트 작업)

연결 관리자에서 엑셀 파일과 연결을 생성합니다. 이후의 작업에서 엑셀 파일의 위치가 필요하기 때문에 엑셀 파일의 위치를 변수에 저장하는 작업을 하도록 하겠습니다.

1.       하단의 연결 관리자 창에서 새 연결…을 추가합니다.

2.       SSIS 연결 관리자 추가 창에서 EXCEL을 선택합니다.

3.       앞에서 만든 ResultData.xls 파일의 경로를 찾아서 선택하여 연결해줍니다.

4.       연결 이름을 TargetExcel 이라고 변경해줍니다.

5.       패키지 범위의 변수를 추가합니다.

이름	데이터 형식	값
ExcelDir	String

 

6.       스크립트 작업을 추가하고 TotalCount 조회와 연결 시켜줍니다. 이름은 엑셀 경로 알아오기로 변경하겠습니다.

7.       엑셀 경로 알아오기를 더블 클릭해서 스크립트 작업 편집기 창을 엽니다. 좌측의 스크립트 메뉴를 선택하고 우측의 ReadWriteVariables 속성에 ExcelDir 변수를 입력합니다.

8.       스크립트 디자인 버튼을 클릭하면 VS for Applications가 열리는데 아래의 소스를 입력합니다.

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime   Public Class ScriptMain       Public Sub Main()         Dts.Variables("ExcelDir").Value = _         Dts.Connections("TargetExcel").Properties("ExcelFilePath").GetValue(Dts.Connections("TargetExcel")).ToString()           Dts.TaskResult = Dts.Results.Success       End Sub   End Class

 

TargetExcel 연결의 ExcelFilePath 속성을 가져오는 스크립트입니다.

.

9.       화면 결과

저는 패키지 명을 ExlSplit2.dtsx 로 만들었기 때문에 변수의 범위가 ExlSplit2로 보여지고 있습니다.

 

- 작업 3. For 루프 컨테이너

1.       패키지 범위의 변수를 추가합니다.

이름	데이터 형식	값
i	Int32	0

 

2.       For 루프 컨테이너를 추가하고 엑셀 경로 알아오기와 연결해줍니다.

3.       For 루프 컨테이너를 더블 클릭해서 편집기를 열고 속성에 아래와 같이 입력합니다.

InitExpression	@i = 0
EvalExpression	@i < @TotalCount
AssignExpression	@i = @i + 60000

한 Sheet에 60,000행의 데이터를 입력하기 위해 60,000씩 증가하는 For 루프를 만들었습니다.

 

- 작업 4. Table명 생성 및 Table Exists 체크 (스크립트 작업)

테이블명은 Data001, Data002, … 식으로 만들 생각입니다. 이 스크립트 작업에서 변수 i의 값을 60,000으로 나눈 몫을 이용해서 테이블명을 생성하겠습니다.

그리고 테이블을 생성할 때 동일한 이름을 가진 테이블이 있는지 체크하는 작업이 필요합니다. 여기서는 기존의 테이블을 삭제하고 다시 생성하도록 하겠습니다.

 

아, 그리고 테이블이 존재하는 지 체크하는 방법은 바로 앞 게시물인 ‘엑셀 파일의 시트 명 알아내기’에서 소개된 ADO.NET 연결 방법이 좋은 것 같습니다. ADO.NET 연결을 이용해서 테이블 목록을 가져오는 방법이 소개됐으므로 여기선 스크립트 작업으로 처리하는 방법을 생각해 보겠습니다.

 

1.       패키지 범위의 변수를 추가합니다.

이름	데이터 형식	값
SheetName	String	Data001
FileExistsYN	Boolean	False
SheetExistsYN	Boolean	False

 

2.       스크립트 작업을 For 루프 컨테이너 안에 추가합니다.
이름은 ‘Sheet명 생성 및 Sheet Exists 체크’로 변경하겠습니다.

3.       Sheet명 생성을 더블 클릭해서 편집기를 엽니다.

4.       좌측 스크립트 메뉴를 선택하고 우측의 ReadOnlyVariables 속성과 ReadWriteVariables 속성의 값을 다음과 같이 설정해줍니다.

ReadOnlyVariables	i, ExcelDir
ReadWriteVariables	SheetName, SheetExistsYN, FileExistsYN

 

5.        스크립트 디자인 버튼을 클릭하여 열린 VS for Applications에 아래의 소스를 입력합니다.

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports System.IO Imports System.Data.OleDb   Public Class ScriptMain       Public Sub Main()         Dim SheetIdx As Integer         Dim dir As String = Dts.Variables("ExcelDir").Value.ToString         Dim sheet As String         Dim connectionString As String         Dim excelCon As OleDbConnection         Dim excelTbl As DataTable         Dim currentTable As String           'Sheet 이름생성         SheetIdx = CInt(CInt(Dts.Variables("i").Value) / 60000) + 1         sheet = "Data" + Right("00" + SheetIdx.ToString(), 3)         Dts.Variables("SheetName").Value = sheet           'Table 존재체크         If File.Exists(dir) Then             Dts.Variables("FileExistsYN").Value = True             connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _                 "Data Source=" & dir & ";Extended Properties=Excel 8.0"             excelCon = New OleDbConnection(connectionString)             excelCon.Open()             excelTbl = excelCon.GetSchema("Tables")               If excelTbl.Select("TABLE_NAME = '" & sheet & "'").Length > 0 Then                 Dts.Variables("SheetExistsYN").Value = True             Else                 Dts.Variables("SheetExistsYN").Value = False             End If               excelCon.Dispose()             excelTbl.Dispose()         Else             Dts.Variables("FileExistsYN").Value = False         End If           Dts.TaskResult = Dts.Results.Success     End Sub   End Class

스크립트를 입력하면 DataTable과 excelCon.GetSchema 부분에서 경고가 뜹니다. 그럼 프로젝트 탐색기에서 참조 폴더에서 오른쪽 클릭으로 참조를 추가합니다. 또는 프로젝트 메뉴에서 참조 추가 메뉴를 선택해서 System.Xml.dll을 추가해줍니다.

그럼 파란 밑줄이 사라집니다