다음을 통해 공유

SSIS(SQL Server Integration Services)를 사용하여 Excel에서 데이터 가져오기 또는 Excel로 데이터 내보내기

  • 아티클

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

이 문서에서는 SSIS(SQL Server Integration Services)를 사용하여 Excel에서 데이터를 가져오거나 Excel로 데이터를 내보내기 위해 제공해야 하는 연결 정보 및 구성해야 하는 설정에 대해 설명합니다.

다음 섹션에는 SSIS에서 Excel을 성공적으로 사용하고 일반적인 문제를 이해하고 해결하는 데 필요한 정보가 포함되어 있습니다.

  1. 사용할 수 있는 도구.

  2. 필요한 파일입니다.

  3. SSIS를 사용하여 Excel에서 데이터를 로드할 때 제공해야 하는 연결 정보 및 구성해야 하는 설정입니다.

  4. 알려진 문제 및 제한 사항입니다.

사용할 수 있는 도구

다음 도구 중 하나를 사용하여 SSIS를 통해 Excel에서 데이터를 가져오거나 Excel로 데이터를 내보낼 수 있습니다.

Excel에 연결하는 데 필요한 파일 가져오기

SSIS를 사용하여 Excel에서 데이터를 가져오거나 Excel로 데이터를 내보내려면 Excel용 연결 구성 요소가 설치되어 있지 않은 경우 먼저 다운로드해야 합니다. Excel의 연결 구성 요소는 기본값으로 설치되지 않습니다.

Office 간편 실행 외부에서 ODBC, OLEDB 또는 DAO 인터페이스에 액세스할 수 없는 내의 표를 사용하여 사용자 환경에 추가 구성 요소가 필요한지 확인합니다.

참고: Office System 드라이버는 특정 시나리오에서만 지원됩니다. 특정 지침은 서버 쪽 Office 자동화에 대한 고려 사항을 참조하세요.

데이터 원본으로 Excel 지정

첫 번째 단계는 Excel에 연결하려는 경우를 나타냅니다.

SSIS에서

SSIS에서 Excel 원본 또는 대상 파일에 연결할 Excel 연결 관리자를 만듭니다. 연결 관리자를 만드는 방법에는 여러 가지가 있습니다.

  • 연결 관리자 영역에서 마우스 오른쪽 단추로 클릭하고 새 연결을 선택합니다. SSIS 연결 관리자 추가 대화 상자에서 EXCEL을 선택한 다음, 추가를 선택합니다.

  • SSIS 메뉴에서 새 연결을 선택합니다. SSIS 연결 관리자 추가 대화 상자에서 EXCEL을 선택한 다음, 추가를 선택합니다.

  • Excel 원본 편집기 또는 Excel 대상 편집기의 연결 관리자 페이지에서 Excel 원본 또는 Excel 대상을 구성하는 동시에 연결 관리자를 만듭니다.

SQL Server 가져오기 및 내보내기 마법사에서

가져오기 및 내보내기 마법사의 데이터 원본 선택 또는 대상 선택 페이지에서 데이터 원본 목록의 Microsoft Excel을 선택합니다.

데이터 원본 목록에 Excel이 표시되지 않는 경우 32비트 마법사를 실행하고 있는지 확인합니다. Excel 연결 구성 요소는 일반적으로 32비트 파일이며 64비트 마법사에는 표시되지 않습니다.

Excel 파일 및 파일 경로

제공하는 첫 번째 정보는 Excel 파일의 경로 및 파일 이름입니다. 이 정보는 SSIS 패키지의 Excel 연결 관리자 편집기 또는 가져오기 및 내보내기 마법사의 데이터 원본 선택 또는 대상 선택 페이지에서 제공합니다.

다음과 같은 형식의 경로 및 파일 이름을 입력합니다.

  • 로컬 컴퓨터의 파일은 C:\TestData.xlsx입니다.

  • 네트워크 공유의 파일은 \\Sales\Data\TestData.xlsx입니다.

또는 찾아보기를 클릭해서 열기 대화 상자를 사용하여 스프레드를 찾습니다.

Important

암호로 보호된 Excel 파일에 연결할 수 없습니다.

Excel 버전

제공하는 두 번째 정보는 Excel 파일의 버전입니다. 이 정보는 SSIS 패키지의 Excel 연결 관리자 편집기 또는 가져오기 및 내보내기 마법사의 데이터 원본 선택 또는 대상 선택 페이지에서 제공합니다.

파일을 만드는 데 사용된 Microsoft Excel 버전 또는 호환되는 다른 버전을 선택합니다. 예를 들면 2016 연결 구성 요소를 설치하는 데 문제가 있는 경우 2010 구성 요소를 설치하고 이 목록에서 Microsoft Excel 2007-2010을 선택할 수 있습니다.

이전 버전의 연결 구성 요소만 설치된 경우 목록에서 최신 Excel 버전을 선택하지 못할 수 있습니다. Excel 버전 목록에는 SSIS에서 지원하는 모든 버전의 Excel이 포함됩니다. 이 목록에 항목이 있으면 필요한 연결 구성 요소가 설치되어 있음을 나타내지 않습니다. 예를 들면 2016 연결 구성 요소를 설치하지 않은 경우에도 Microsoft Excel 2016이 목록에 표시됩니다.

첫 행에 열 이름 포함

Excel에서 데이터를 가져오기하는 경우 다음 단계는 데이터의 첫 번째 행에 열 이름이 포함되어 있는지 여부를 나타냅니다. 이 정보는 SSIS 패키지의 Excel 연결 관리자 편집기 또는 가져오기 및 내보내기 마법사의 데이터 원본 선택 페이지에서 제공합니다.

  • 원본 데이터가 열 이름이 포함되지 않으므로 이 옵션을 사용하지 않으면 마법사는 F1, F2 등을 열 머리글로 사용합니다.
  • 데이터에 열 이름이 포함되어 있지만 이 옵션을 비활성화한 경우 마법사는 열 이름을 데이터의 첫 번째 행으로 가져옵니다.
  • 데이터에 열 이름이 포함되지 않지만 이 옵션을 사용하도록 설정하면 마법사는 원본 데이터의 첫 번째 행을 열 이름으로 사용합니다. 이 경우 원본 데이터의 첫 번째 행은 더 이상 데이터 자체에 포함되지 않습니다.

Excel에서 데이터를 내보내기하는 경우 이 옵션을 사용하도록 설정하면 내보내기한 데이터의 첫 번째 행에 열 이름이 포함됩니다.

워크시트 및 범위

데이터의 원본 또는 대상으로 사용할 수 있는 Excel 개체에는 세 가지 유형, 워크시트, 명명된 범위 또는 해당 주소로 지정한 명명되지 않은 셀 범위가 있습니다.

  • 워크시트. 워크시트를 지정하려면 시트 이름 끝에 $ 문자를 추가하고 문자열 주위에 구분 기호(예: [Sheet1$])를 추가합니다. 또는 기존 테이블 및 뷰 목록에서 $ 문자로 끝나는 이름을 찾습니다.

  • 명명된 범위. 명명된 범위를 지정하려면 범위 이름(예: MyDataRange)을 제공합니다. 또는 기존 테이블 및 뷰 목록에서 $ 문자로 끝나지 않는 이름을 찾습니다.

  • 명명되지 않은 범위. 명명하지 않은 셀의 범위를 지정하려면 시트 이름 끝에 $ 문자를 추가하고 문자열 주위에 구분 기호를 추가합니다(예: [Sheet1$A1:B4]).

데이터의 원본 또는 대상으로 사용할 Excel 개체의 형식을 선택하거나 지정하려면 다음 중 하나를 수행합니다.

SSIS에서

SSIS에서 Excel 원본 편집기 또는 Excel 대상 편집기의 연결 관리자 페이지에서 다음 작업 중 하나를 수행합니다.

  • 워크시트 또는 명명된 범위를 사용하려면 테이블 또는 뷰를 데이터 액세스 모드로 선택합니다. 그런 다음 Excel 시트 목록의 이름에서 워크시트 또는 명명된 범위를 선택합니다.

  • 해당 주소로 지정한 명명되지 않은 범위를 사용하려면 SQL 명령을 데이터 액세스 모드로 선택합니다. 그런 다음 SQL 명령 텍스트 필드에 다음 예제와 같은 쿼리를 입력합니다.

    SELECT * FROM [Sheet1$A1:B5]

SQL Server 가져오기 및 내보내기 마법사에서

가져오기 및 내보내기 마법사에서 다음 중 하나를 수행합니다.

  • Excel에서 가져오기하는 경우 다음 중 하나를 수행합니다.

    • 워크시트 또는 명명된 범위를 사용하려면 테이블 복사 또는 쿼리 지정 페이지에서 하나 이상의 테이블 또는 뷰에서 데이터 복사를 선택합니다. 그런 다음 원본 테이블 및 뷰 선택 페이지의 원본 열에서 원본 워크시트 및 명명된 범위를 선택합니다.

    • 해당 주소로 지정한 명명되지 않은 범위를 사용하려면 테이블 복사 또는 쿼리 지정 페이지에서 전송할 데이터를 지정할 쿼리 작성을 선택합니다. 그런 다음 원본 쿼리 제공 페이지에서 다음 예제와 유사한 쿼리를 제공합니다.

      SELECT * FROM [Sheet1$A1:B5]

  • Excel로 내보내기하는 경우 다음 중 하나를 수행합니다.

    • 워크시트 또는 명명된 범위를 사용하려면 원본 테이블 및 뷰 선택 페이지에 있는 대상 열에서 대상 워크시트 및 명명된 범위를 선택합니다.

    • 해당 주소로 지정한 명명되지 않은 범위를 사용하려면 원본 테이블 및 뷰 선택 페이지의 대상 열에서 Sheet1$A1:B5 구분 기호 없이 다음 형식으로 범위를 입력합니다. 마법사에서 구분 기호를 추가합니다.

가져오기나 내보내기할 Excel 개체를 선택하거나 입력한 후 마법사의 원본 테이블 및 뷰 선택 페이지에서 다음 작업을 수행할 수도 있습니다.

  • 매핑 편집을 선택하여 원본과 대상 간에 열 매핑을 검토합니다.

  • 미리 보기를 선택해서 샘플 데이터를 미리 보기하여 예상하는 데이터인지 확인합니다.

데이터 형식 문제

데이터 형식

Excel 드라이버는 제한된 데이터 형식 집합만 인식합니다. 예를 들면 모든 숫자 열은 double(DT_R8)으로 해석되며 모든 문자열 열(메모 열 제외)은 255자 유니코드 문자열(DT_WSTR)로 해석됩니다. SSIS는 다음과 같이 Excel 데이터 형식을 매핑합니다.

  • 숫자 - 배정밀도 부동(DT_R8)

  • 통화 - 통화(DT_CY)

  • 부울 - 부울(DT_BOOL)

  • 날짜/시간 - datetime(DT_DATE)

  • 문자열 - 길이가 255인 유니코드 문자열(DT_WSTR)

  • 메모 - 유니코드 텍스트 스트림(DT_NTEXT)

데이터 형식 및 길이 변환

SSIS는 데이터 형식을 암시적으로 변환하지 않습니다. 따라서 파생 열 변환이나 데이터 변환을 사용하여 Excel 데이터를 Excel이 아닌 대상으로 로드하기 전에 명시적으로 변환하거나 또는 Excel이 아닌 원본의 데이터를 Excel 대상으로 로드하기 전에 변환해야 할 수 있습니다.

필요할 수 있는 일부 변환 예는 다음과 같습니다.

  • 유니코드 Excel 문자열 열과 특정 코드 페이지가 있는 비유니코드 문자열 열 간 변환

  • 255자 Excel 문자열 열과 길이가 다른 문자열 열 간 변환입니다.

  • 배정밀도 Excel 숫자 열 및 다른 형식의 숫자 열 간 변환입니다.

팁

가져오기 및 내보내기 마법사를 사용하고 이러한 변환의 일부가 필요한 데이터의 경우 마법사에서 필요한 변환을 구성합니다. 따라서 SSIS 패키지를 사용하려는 경우에도 가져오기 및 내보내기 마법사를 사용하여 초기 패키지를 만드는 것이 유용할 수 있습니다. 마법사에서 연결 관리자, 원본, 변환 및 대상을 만들고 구성하도록 할 수 있습니다.

가져오기 문제

빈 행

워크시트 또는 원본으로 명명된 범위를 지정하면 드라이버는 워크시트 또는 범위의 가장 왼쪽에서 비어 있지 않은 첫 번째 셀부터 인접한 블록의 셀을 읽습니다. 따라서 데이터가 행 1에서 시작할 필요는 없지만 원본 데이터에 빈 행이 있을 수는 없습니다. 예를 들면 열 머리글과 데이터 행 사이에 빈 행이나 제목 다음에 워크시트 맨 위에 빈 행이 있을 수 없습니다.

데이터 위에 행이 빈 경우 워크시트로 데이터를 쿼리할 수 없습니다. Excel에서는 데이터 범위를 선택하고 범위에 이름을 할당한 다음 워크시트 대신 명명된 범위를 쿼리해야 합니다.

누락 값

Excel 드라이버는 지정한 원본에서 특정 개수의 행(기본값: 8개 행)을 읽어 각 열의 데이터 형식을 추측합니다. 열에 혼합된 데이터 형식, 특히 텍스트 데이터와 혼합된 숫자 데이터가 포함된 것으로 표시되면 드라이버는 대부분의 데이터 형식을 선호하도록 결정하고 다른 형식의 데이터가 포함된 셀에 대해 null 값을 반환합니다. (동등한 경우, 숫자 형식이 우선합니다.) Excel 워크시트에서 대부분의 셀 서식 옵션은 이 데이터 형식 결정에 영향을 미치지 않는 것으로 보입니다.

가져오기 모드를 지정하여 모든 값을 텍스트로 가져오기해서 Excel 드라이버의 이 동작을 수정할 수 있습니다. 가져오기 모드를 지정하려면 속성 창에서 Excel 연결 관리자의 연결 문자열에 있는 확장 속성 값에 IMEX=1을 추가합니다.

잘린 텍스트

드라이버가 Excel 열에 텍스트 데이터가 포함되어 있는지 확인하면 드라이버는 샘플링하는 가장 긴 값에 따라 데이터 형식(문자열 또는 메모)을 선택합니다. 드라이버가 샘플링하는 행에서 255자보다 긴 값을 검색하지 않는 경우 열을 메모 열 대신 255자 문자열 열로 처리합니다. 따라서 255자보다 긴 값은 잘릴 수 있습니다.

잘리지 않고 메모 열에서 데이터를 가져오기하려면 다음 두 가지 옵션이 있습니다.

  • 샘플링된 행 중 하나 이상의 메모 열에 255자보다 긴 값이 포함되어 있는지 확인

  • 드라이버에서 샘플링한 행 수를 늘려 이러한 행을 포함합니다. 다음 레지스트리 키에서 TypeGuessRows 값을 늘려 샘플링된 행 수를 늘릴 수 있습니다.

재배포 가능 구성 요소 버전 레지스트리 키
Excel 2016 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel
Excel 2010 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

내보내기 문제

새 대상 파일 만들기

SSIS에서

만들려는 새 Excel 파일의 경로 및 파일 이름으로 Excel 연결 관리자를 만듭니다. 그런 다음 Excel 대상 편집기에서 Excel 시트의 이름에 대해 새로 만들기를 선택하여 대상 워크시트를 만듭니다. 이 시점에서 SSIS는 지정된 워크시트를 사용하여 새 Excel 파일을 만듭니다.

SQL Server 가져오기 및 내보내기 마법사에서

대상 선택 페이지에서 찾아보기를 선택합니다. 열기 대화 상자에서 새 Excel 파일을 만들 폴더로 이동하고 새 파일의 이름을 입력한 다음 열기를 선택합니다.

충분히 큰 범위로 내보내기

대상으로 범위를 지정하는 경우 범위가 원본 데이터의 열 수보다 적으면 오류가 발생합니다. 그러나 지정한 범위에 원본 데이터보다 적은 행 이 있는 경우 마법사는 오류 없이 행을 계속 작성하고 범위 정의를 새 행 수와 일치하도록 확장합니다.

긴 텍스트 값 내보내기

Excel 열에 255자보다 긴 문자열을 저장하려면 먼저 드라이버가 대상 열의 데이터 형식을 문자열이 아닌 메모로 인식해야 합니다.

  • 기존 대상 테이블에 이미 데이터 행이 포함되어 있는 경우 드라이버에서 샘플링한 처음 몇 개의 행에는 메모 열에 255자보다 긴 값의 인스턴스가 하나 이상 포함되어야 합니다.

관련 콘텐츠

이 문서에 설명된 구성 요소 및 절차에 대한 자세한 내용은 다음 문서를 참조하세요.

SSIS 정보

Excel 연결 관리자
Excel 원본
Excel 대상
Foreach 루프 컨테이너를 사용하여 Excel 파일 및 테이블 루핑
스크립트 태스크를 사용한 Excel 파일 작업

SQL Server 가져오기 및 내보내기 마법사 정보

Excel 데이터 원본에 연결
가져오기 및 내보내기 마법사의 이 간단한 예제로 시작

Excel에서 SQL Server 또는 Azure SQL Database로 데이터 가져오기