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

이 문서에서는 SSIS(SQL Server Integration Services)를 사용하여 Excel에서 데이터를 가져오거나 Excel로 데이터를 내보내는 방법을 설명합니다. 또한 이 문서에서는 필수 구성 요소, 제한 사항 및 알려진 문제에 대해서도 설명합니다.

SSIS 패키지를 만들고 Excel 연결 관리자와 Excel 원본 또는 Excel 대상을 사용하여 Excel에서 데이터를 가져오거나 Excel로 데이터를 내보낼 수 있습니다. SSIS를 기반으로 하는 SQL Server 가져오기 및 내보내기 마법사를 사용할 수도 있습니다.

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

1. 필요한 파일입니다.
2. Excel에서 또는 Excel로 데이터를 로드할 때 제공해야 하는 정보입니다.
   • Excel 을 데이터 원본으로 지정합니다.
   • Excel 파일 이름 및 경로를 제공합니다.
   • Excel 버전을 선택합니다.
   • 첫 번째 행에 열 이름이 포함되는지 여부를 지정합니다.
   • 데이터가 포함된 워크시트 또는 범위를 제공합니다.
3. 알려진 문제 및 제한 사항입니다.
   • 데이터 형식과 관련된 문제입니다.
   • 가져오기 문제
   • 내보내기 관련 문제입니다.

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

Excel에서 데이터를 가져오거나 Excel로 데이터를 내보내려면 Excel용 연결 구성 요소가 아직 설치되어 있지 않은 경우 다운로드해야 할 수 있습니다. Excel의 연결 구성 요소는 기본적으로 설치되지 않습니다.

Microsoft Access 데이터베이스 엔진 2016 재배포 가능 패키지에서 Excel용 연결 구성 요소의 최신 버전을 다운로드합니다.

최신 버전의 구성 요소는 이전 버전의 Excel에서 만든 파일을 열 수 있습니다.

Microsoft Access 2016 런타임이 아닌 Access 데이터베이스 엔진 2016 재배포 가능 파일을 다운로드해야 합니다.

컴퓨터에 이미 32비트 버전의 Office가 있는 경우 32비트 버전의 구성 요소를 설치해야 합니다. 또한 SSIS 패키지를 32비트 모드로 실행하거나 32비트 버전의 가져오기 및 내보내기 마법사를 실행해야 합니다.

Office 365 구독이 있는 경우 설치 관리자를 실행할 때 오류 메시지가 표시 될 수 있습니다. 이 오류는 Office 간편 실행 구성 요소와 함께 다운로드를 설치할 수 없다는 것을 나타냅니다. 이 오류 메시지를 무시하려면 명령 프롬프트 창을 열고 다운로드한 .EXE 파일을 /quiet 스위치를 사용하여 무음 모드로 실행합니다. 다음은 그 예입니다.

C:\Users\<user name>\Downloads\AccessDatabaseEngine.exe /quiet

2016 재배포 가능 파일을 설치하는 데 문제가 있는 경우 여기에서 Microsoft Access Database Engine 2010 Redistributable를 설치하여 대신 2010 재배포 가능 파일을 사용하십시오. (Excel 2013에는 재배포할 수 없습니다.)

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.

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

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 이외의 원본에서 데이터를 변환하려면 파생 열 또는 데이터 변환을 사용해야 할 수 있습니다.

다음은 필요할 수 있는 변환의 몇 가지 예입니다.

• 특정 코드 페이지를 사용하여 유니코드 Excel 문자열 열과 유니코드가 아닌 문자열 열 간 변환

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

• 배정밀도 Excel 숫자 열과 다른 형식의 숫자 열 간 변환

가져오기 관련 문제

빈 행

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

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

누락된 값

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

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

잘린 텍스트

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

잘림 없이 메모 열에서 데이터를 가져오려면 다음 두 가지 옵션이 있습니다.

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

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

내보내기 관련 문제

새 대상 파일 만들기

SSIS에서

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

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

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

충분히 넓은 범위로 내보내기

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

긴 텍스트 값 내보내기

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

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

• 패키지 디자인 중이나 실행 중 또는 가져오기 및 내보내기 마법사에서 새 대상 테이블이 생성된 경우 CREATE TABLE 문은 LONGTEXT(또는 해당 동의어 중 하나)를 대상 메모 열의 데이터 형식으로 사용해야 합니다. 마법사에서, 열 매핑 페이지의 대상 테이블 만들기 옵션 옆에 있는 SQL 편집을 클릭하여, 필요에 따라 CREATE TABLE 문을 확인하고 수정합니다.

관련 콘텐츠

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

SSIS 정보

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

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

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

기타 문서

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