다음을 통해 공유

Foreach 루프 컨테이너를 사용하여 Excel 파일 및 테이블 루핑

  • 아티클

적용 대상: Azure Data Factory의 SQL Server SSIS Integration Runtime

이 항목의 절차에서는 적절한 열거자와 함께 Foreach 루프 컨테이너를 사용하여 폴더 내의 Excel 통합 문서 또는 Excel 통합 문서 내의 테이블을 루핑하는 방법에 대해 설명합니다.

Important

Excel 파일 연결 및 Excel 파일에서 데이터를 로드할 때 제한 사항 및 알려진 문제에 대한 자세한 내용은 SSIS(SQL Server Integration Services)를 통해 Excel로 데이터 로드를 참조하세요.

Foreach 파일 열거자를 사용하여 Excel 파일을 반복하는 방법

  1. 루프 반복마다 현재 Excel 경로 및 파일 이름을 받을 문자열 변수를 만듭니다. 유효성 검사 문제를 방지하려면 변수의 초기 값으로 유효한 Excel 경로 및 파일 이름을 할당하십시오. (이 절차의 뒷부분에 표시된 샘플 식은 변수 이름이 ExcelFile입니다.)

  2. 필요에 따라 Excel 연결 문자열 확장 속성 인수의 값을 보유할 다른 문자열 변수를 만듭니다. 이 인수는 Excel 버전을 지정하고 첫 번째 행에 열 이름을 포함할지 여부와 가져오기 모드 사용 여부를 결정하는 일련의 값을 포함합니다. (이 절차의 뒷부분에 표시된 샘플 식은 변수 이름이 ExtProperties, 초기 값은 "Excel 12.0;HDR=Yes"입니다.)

    확장 속성 인수에 변수를 사용하지 않는 경우 연결 문자열을 포함하는 식에 변수를 수동으로 추가해야 합니다.

  3. 제어 흐름 탭에 Foreach 루프 컨테이너를 추가합니다. Foreach 루프 컨테이너를 구성하는 방법에 대한 자세한 내용은 Foreach 루프 컨테이너 구성을 참조하세요.

  4. Foreach 루프 편집기의 컬렉션 페이지에서 Foreach File 열거자를 선택하고 Excel 통합 문서가 있는 폴더를 지정한 다음 파일 필터(일반적으로 *.xlsx)를 지정합니다.

  5. 변수 매핑 페이지에서 루프 반복마다 현재 Excel 경로 및 파일 이름을 받는 사용자 정의 문자열 변수에 인덱스 0을 매핑합니다. (이 절차의 뒷부분에 표시된 샘플 식은 변수 이름이 ExcelFile입니다.)

  6. Foreach 루프 편집기를 닫습니다.

  7. 패키지의 연결 관리자 추가, 삭제 또는 공유에 설명된 대로 패키지에 Excel 연결 관리자를 추가합니다. 유효성 검사 오류를 방지하려면 연결에 대한 기존 Excel 통합 문서 파일을 선택합니다.

    Important

    이 Excel 연결 관리자를 사용하는 작업 및 데이터 흐름 구성 요소를 구성할 때 유효성 검사 오류를 방지하려면 Excel 연결 관리자 편집기에서 기존 Excel 통합 문서를 선택합니다. 다음 단계에 설명된 대로 ConnectionString 속성에 대한 식을 구성한 후에는 연결 관리자가 런타임에 이 통합 문서를 사용하지 않습니다. 패키지를 만들고 구성한 후 속성 창에서 ConnectionString 속성 값을 지울 수 있습니다. 그러나 이 값을 지우면 Foreach 루프가 실행될 때까지 Excel 연결 관리자의 연결 문자열 속성이 더 이상 유효하지 않습니다. 따라서 유효성 검사 오류를 방지하려면 연결 관리자가 사용되는 작업 또는 패키지에서 DelayValidation 속성을 True로 설정해야 합니다.

    또한 Excel 연결 관리자의 False 속성에 기본값 RetainSameConnection 를 사용해야 합니다. 이 값을 True로 변경하면 루프의 각 반복에서 첫 번째 Excel 통합 문서가 계속 열립니다.

  8. 새 Excel 연결 관리자를 선택하고 속성 창에서 식 속성을 클릭한 다음 줄임표를 클릭합니다.

  9. 속성 식 편집기에서 ConnectionString 속성을 선택한 다음 줄임표를 클릭합니다.

  10. 식 작성기에서 다음 식을 입력합니다.

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=\"" + @[User::ExtProperties] + "\""

    이스케이프 문자 "\"를 사용하여 확장 속성 인수의 값에 필요한 내부 따옴표를 이스케이프합니다.

    확장 속성 인수는 선택 사항이 아닙니다. 변수를 사용하여 해당 값을 포함하지 않는 경우 다음 예제와 같이 식에 수동으로 추가해야 합니다.

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=Excel 12.0"

  11. 지정된 파일 위치 및 패턴과 일치하는 각 Excel 통합 문서에서 Excel 연결 관리자를 사용하여 동일한 작업을 수행하는 Foreach 루프 컨테이너에 작업을 만듭니다.

Foreach ADO.NET 스키마 행 집합 열거자를 사용하여 Excel 테이블을 반복하는 방법

  1. Microsoft ACE OLE DB 공급자를 사용하여 Excel 통합 문서에 연결하는 ADO.NET 연결 관리자를 만듭니다. 연결 관리자 대화 상자의 모든 페이지에서 Excel 버전을 확장 속성의 값으로 입력합니다(이 경우에 Excel 12.0). 자세한 내용은 패키지에서 연결 관리자 추가, 삭제 또는 공유를 참조하세요.

  2. 루프의 각 반복에서 현재 테이블의 이름을 받을 문자열 변수를 만듭니다.

  3. 제어 흐름 탭에 Foreach 루프 컨테이너를 추가합니다. Foreach 루프 컨테이너를 구성하는 방법에 대한 자세한 내용은 Foreach 루프 컨테이너 구성을 참조하세요.

  4. Foreach 루프 편집기의 컬렉션 페이지에서 Foreach ADO.NET 스키마 행 집합 열거자를 선택합니다.

  5. 연결 값으로 이전에 만든 ADO.NET 연결 관리자를 선택합니다.

  6. 스키마 값으로 테이블을 선택합니다.

    참고 항목

    Excel 통합 문서의 테이블 목록에는 워크시트($ 접미사가 있는) 및 명명된 범위가 모두 포함됩니다. 워크시트만 포함하거나 명명된 범위 목록만 포함하도록 목록을 필터링해야 하는 경우에는 스크립트 태스크에서 이를 위한 사용자 지정 코드를 작성해야 합니다. 자세한 내용은 스크립트 태스크를 사용하여 Excel 파일 작업을 참조하세요.

  7. 변수 매핑 페이지에서 인덱스 2를 이전에 만든 문자열 변수에 매핑하여 현재 테이블의 이름을 저장합니다.

  8. Foreach 루프 편집기를 닫습니다.

  9. 지정된 통합 문서의 각 Excel 테이블에서 Excel 연결 관리자를 사용하여 동일한 작업을 수행하는 Foreach 루프 컨테이너에 작업을 만듭니다. 스크립트 태스크를 사용하여 열거된 테이블 이름을 검사하거나 각 테이블을 사용하는 경우 스크립트 태스크의 ReadOnlyVariables 속성에 문자열 변수를 추가해야 합니다.

참고 항목

SSIS(SQL Server Integration Services)를 사용하여 Excel에서 또는 Excel로 데이터 로드
Foreach 루프 컨테이너 구성
속성 식 추가 또는 변경
Excel 연결 관리자
Excel 원본
Excel 대상
스크립트 태스크를 사용한 Excel 파일 작업