SSIS를 이용하여 Excel 데이터 DB로 넣기

SSIS와 SQL Agent를 이용하여 특정 시간마다 Excel 파일을 로드하여 데이터를 DB로 밀어넣는 Job을 생성

0. 패키지에 패스워드를 설정한다.

- 패스워드를 설정해야 Agent에 물릴 수 있다.

- ProtectionLevel 속성을 EncryptSensitiveWithPassword 로 수정

- PageagePassword 속성의 ...을 클릭하여 패스워드 설정

1. 연결관리자

- DB, Excel, File 연결 관리자가 필요하다.

2. Excel 파일을 담을 변수가 필요하다.

- 값으로 유효한 Excel File ConnectionString를 넣어줘야 유효성 검사에서 실패하지 않는다.

- 유효한 ConnectionString은 Excel 연결 관리자 초기 세팅 후 값을 복사해서 가져왔다.

3. 제어흐름 작업

- Foreach 루프로 Excel 파일이 있는 만큼 실행하도록 한다.

- Excel 파일을 로드해서 작업을 하고 다른 경로로 파일을 이동한다.

- 모든 Excel 작업이 끝나면 sp를 실행하여 데이터 변환 가공하여 테이블에 저장한다. ( 이부분을 SSIS에서 해도 되지만 본인은 쿼리가 편해서..... )

- Foreach Loop 속성 변경

         

- 파일 시스템 태스크 속성 변경

    a. DestinationConnection 속성 File 연결 관리자 세팅

    b. IsSourcePathVariable 속성을 True로 하고 SourceVariable 를 아래에서 만들 변수 지정

4. 데이터흐름 작업

- Excel 을 로드한다.

- 데이터 형변환을 한다.

- DB 가공 테이블에 데이터를 삽입한다.

5. 작업이 계속 실패하여 데이터 흐름 작업 부분만 떼서 단위 테스트를 진행

-  Excel 파일관리자를 편집하여 실행

- 에러 발생

- 64비트버전에서 Excel 관리자를 사용할 수 없다는...

6. 솔루션 속성에서 32bit 실행가능 하도록 수정

                               

7. 솔루션 속성 수정 후 정상적으로 실행

8. Excel 파일 관리자가 동적으로 파일을 로드하도록 속성 편집

-  Expressions 에 ...을 클릭하여 ConnectionString 속성값에 위에서 만든 변수를 포함해

   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::filename] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"

   식 생성

- DelayVaildation 속성 Ture로 변경 (런타임시 유효성을 검사하도록 하는 옵션)

- Excel 원본의 ValidateExternalMetedata 속성 false로 변경 (런타임시 유효성을 검사하도록 하는 옵션)

9. SQL Agent에 Job 설정

- 단계에서 SQL Server Integration Services 패키지 선택

- 패키지 원본 파일시스템 선택

- 패키지 경로 지정

- 다른 탭으로 이동하면 패스워드 입력창이 뜨는데 맨 위에서 지정한 패키지 패스워드 입력

- 일정을 등록하여 Job 생성

10. 생성된 Job 실행했지만 실패

- Agent 오류 로그를 보면 역시 64비트 연결관리자 어쩌고 저쩌고...

여기까지 설정하면 정상적으로 실행된다.