스크립트 태스크를 사용한 Excel 파일 작업

아티클
07/04/2024

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

Integration Services는 스프레드시트에 저장된 데이터를 Microsoft Excel 파일 형식으로 사용하기 위한 Excel 연결 관리자, Excel 원본 및 Excel 대상을 제공합니다. 이 항목에서는 스크립트 태스크를 사용하여 사용 가능한 Excel 데이터베이스(통합 문서 파일) 및 테이블(워크시트 및 명명된 범위)에 대한 정보를 가져오는 기술을 설명합니다.

Important

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

팁

여러 패키지에서 다시 사용할 수 있는 작업을 만들려면 이 스크립트 태스크 샘플의 코드를 사용자 지정 작업의 시작점으로 사용하는 것이 좋습니다. 자세한 내용은 사용자 지정 작업 개발을 참조하세요.

샘플을 테스트하도록 패키지 구성

이 항목의 모든 샘플을 테스트하도록 단일 패키지를 구성할 수 있습니다. 샘플은 동일한 패키지 변수와 동일한 .NET Framework 클래스를 많이 사용합니다.

패키지를 이 항목의 예에 사용할 수 있도록 구성하려면

SSDT(SQL Server Data Tools)에서 새 Integration Services 프로젝트를 만들고 편집을 위해 기본 패키지를 엽니다.
변수 변수 창을 열고 다음 변수를 정의합니다.
- ExcelFile, String 형식. 기존 Excel 통합 문서의 전체 경로와 파일 이름을 입력합니다.
- ExcelTable, String 형식. 기존 워크시트의 이름이나 ExcelFile 변수의 값에 명명된 통합 문서의 명명된 범위를 입력합니다. 이 값은 대/소문자를 구분합니다.
- ExcelFileExists- 부울 형식입니다.
- ExcelTableExists- 부울 형식입니다.
- ExcelFolder, String 형식. 하나 이상의 Excel 통합 문서가 포함된 폴더의 전체 경로를 입력합니다.
- ExcelFiles, Object 형식.
- ExcelTables, Object 형식.
문을 가져옵니다. 대부분의 코드 샘플에서는 스크립트 파일 맨 위에 있는 다음 .NET Framework 네임스페이스 중 하나 또는 둘 다를 가져와야 합니다.
- 파일 시스템 작업의 경우 System.IO.
- System.Data.OleDb를 사용하여 Excel 파일을 데이터 원본으로 엽니다.
References. Excel 파일에서 스키마 정보를 읽는 코드 예제에는 스크립트 프로젝트에는 System.Xml 네임스페이스에 대한 추가 참조가 필요합니다.
옵션 대화 상자의 일반 페이지에 있는 스크립트 언어 옵션을 사용하여 스크립트 구성 요소에 대한 기본 스크립트 언어를 설정합니다. 자세한 정보는 일반 페이지를 참조하세요.

예제 1 설명: Excel 파일이 있는지 확인

이 예에서는 ExcelFile 변수에 지정된 Excel 통합 문서 파일이 존재하는지 확인한 다음 ExcelFileExists 변수의 부울 값을 이 결과로 설정합니다. 패키지의 워크플로에서 분기하는 데 이 부울 값을 사용할 수 있습니다.

이 스크립트 태스크 예제를 구성하려면

패키지에 새 스크립트 작업을 추가하고 해당 이름을 ExcelFileExists로 변경합니다.
스크립트 태스크 편집기의 스크립트 탭에서 ReadOnlyVariables를 클릭하고 다음 방법 중 하나를 사용하여 속성 값을 입력합니다.
- ExcelFile을 입력 합니다.
  
  또는
- 속성 필드 옆의 줄임표(...) 단추를 클릭하고 변수 선택 대화 상자에서 ExcelFile 변수를 선택합니다.
ReadWriteVariables를 클릭하고 다음 방법 중 하나를 사용하여 속성 값을 입력합니다.
- ExcelFileExists를 입력합니다.
  
  또는
- 속성 필드 옆의 줄임표(...) 단추를 클릭하고 변수 선택 대화 상자에서 ExcelFileExists 변수를 선택합니다.
스크립트 편집을 클릭하여 스크립트 편집기를 엽니다.
System.IO 네임스페이스에 대한 Imports 문을 스크립트 파일의 맨 위에 추가합니다.
다음 코드를 추가합니다.

예제 1 코드

Public Class ScriptMain  
  Public Sub Main()  
    Dim fileToTest As String  
  
    fileToTest = Dts.Variables("ExcelFile").Value.ToString  
    If File.Exists(fileToTest) Then  
      Dts.Variables("ExcelFileExists").Value = True  
    Else  
      Dts.Variables("ExcelFileExists").Value = False  
    End If  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class

public class ScriptMain  
{  
  public void Main()  
  {  
    string fileToTest;  
  
    fileToTest = Dts.Variables["ExcelFile"].Value.ToString();  
    if (File.Exists(fileToTest))  
    {  
      Dts.Variables["ExcelFileExists"].Value = true;  
    }  
    else  
    {  
      Dts.Variables["ExcelFileExists"].Value = false;  
    }  
  
    Dts.TaskResult = (int)ScriptResults.Success;  
  }  
}

예제 2 설명: Excel 테이블이 있는지 확인

다음은 변수에 지정된 Excel 워크시트 또는 명명된 범위가 변수에 ExcelTable 지정된 ExcelFile Excel 통합 문서 파일에 있는지 여부를 확인한 다음 변수의 ExcelTableExists 부울 값을 결과로 설정하는 예제입니다. 패키지의 워크플로에서 분기하는 데 이 부울 값을 사용할 수 있습니다.

이 스크립트 태스크 예제를 구성하려면

패키지에 새 스크립트 태스크를 추가하고 해당 이름을 ExcelTableExists로 바꿉니다.
스크립트 태스크 편집기의 스크립트 탭에서 ReadOnlyVariables를 클릭하고 다음 방법 중 하나를 사용하여 속성 값을 입력합니다.
- ExcelTable 및 ExcelFile을 쉼표로 구분하여 입력합니다.
  
  또는
- 속성 필드 옆의 줄임표(...) 단추를 클릭하고 변수 선택 대화 상자에서 ExcelTable 및 ExcelFile 변수를 선택합니다.
ReadWriteVariables를 클릭하고 다음 방법 중 하나를 사용하여 속성 값을 입력합니다.
- ExcelTableExists를 입력 합니다.
  
  또는
- 속성 필드 옆의 줄임표(...) 단추를 클릭하고 변수 선택 대화 상자에서 ExcelTableExists 변수를 선택합니다.
스크립트 편집을 클릭하여 스크립트 편집기를 엽니다.
스크립트 프로젝트에서 System.Xml 어셈블리에 대한 참조를 추가합니다.
System.IO 및 System.Data.OleDb 네임스페이스에 대한 Imports 문을 스크립트 파일 맨 위에 추가합니다.
다음 코드를 추가합니다.

예제 2 코드

Public Class ScriptMain  
  Public Sub Main()  
    Dim fileToTest As String  
    Dim tableToTest As String  
    Dim connectionString As String  
    Dim excelConnection As OleDbConnection  
    Dim excelTables As DataTable  
    Dim excelTable As DataRow  
    Dim currentTable As String  
  
    fileToTest = Dts.Variables("ExcelFile").Value.ToString  
    tableToTest = Dts.Variables("ExcelTable").Value.ToString  
  
    Dts.Variables("ExcelTableExists").Value = False  
    If File.Exists(fileToTest) Then  
      connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _  
        "Data Source=" & fileToTest & _  
        ";Extended Properties=Excel 12.0"  
      excelConnection = New OleDbConnection(connectionString)  
      excelConnection.Open()  
      excelTables = excelConnection.GetSchema("Tables")  
      For Each excelTable In excelTables.Rows  
        currentTable = excelTable.Item("TABLE_NAME").ToString  
        If currentTable = tableToTest Then  
          Dts.Variables("ExcelTableExists").Value = True  
        End If  
      Next  
    End If  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class

public class ScriptMain  
{  
    public void Main()  
        {  
            string fileToTest;  
            string tableToTest;  
            string connectionString;  
            OleDbConnection excelConnection;  
            DataTable excelTables;  
            string currentTable;  
  
            fileToTest = Dts.Variables["ExcelFile"].Value.ToString();  
            tableToTest = Dts.Variables["ExcelTable"].Value.ToString();  
  
            Dts.Variables["ExcelTableExists"].Value = false;  
            if (File.Exists(fileToTest))  
            {  
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +  
                "Data Source=" + fileToTest + ";Extended Properties=Excel 12.0";  
                excelConnection = new OleDbConnection(connectionString);  
                excelConnection.Open();  
                excelTables = excelConnection.GetSchema("Tables");  
                foreach (DataRow excelTable in excelTables.Rows)  
                {  
                    currentTable = excelTable["TABLE_NAME"].ToString();  
                    if (currentTable == tableToTest)  
                    {  
                        Dts.Variables["ExcelTableExists"].Value = true;  
                    }  
                }  
            }  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
  
        }  
}

예제 3 설명: 폴더에서 Excel 파일 목록 가져오기

다음은 변수 값 ExcelFolder 에 지정된 폴더에 있는 Excel 파일 목록으로 배열을 채운 다음 변수에 ExcelFiles 배열을 복사하는 예제입니다. 변수 열거자의 Foreach를 사용하여 배열의 파일을 반복할 수 있습니다.

이 스크립트 태스크 예제를 구성하려면

패키지에 새 스크립트 작업을 추가하고 해당 이름을 GetExcelFiles로 변경합니다.
스크립트 태스크 편집기를 열고 스크립트 탭에서 ReadOnlyVariables를 클릭한 후 다음 방법 중 하나를 사용하여 속성 값을 입력합니다.
- ExcelFolder 형식
  
  또는
- 속성 필드 옆의 줄임표(...) 단추를 클릭하고 변수 선택 대화 상자에서 ExcelFolder 변수를 선택합니다.
ReadWriteVariables를 클릭하고 다음 방법 중 하나를 사용하여 속성 값을 입력합니다.
- ExcelFiles를 입력 합니다.
  
  또는
- 속성 필드 옆의 줄임표(...) 단추를 클릭하고 변수 선택 대화 상자에서 ExcelFiles 변수를 선택합니다.
스크립트 편집을 클릭하여 스크립트 편집기를 엽니다.
System.IO 네임스페이스에 대한 Imports 문을 스크립트 파일의 맨 위에 추가합니다.
다음 코드를 추가합니다.

예 3 코드

Public Class ScriptMain  
  Public Sub Main()  
    Const FILE_PATTERN As String = "*.xlsx"  
  
    Dim excelFolder As String  
    Dim excelFiles As String()  
  
    excelFolder = Dts.Variables("ExcelFolder").Value.ToString  
    excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN)  
  
    Dts.Variables("ExcelFiles").Value = excelFiles  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class

public class ScriptMain  
{  
  public void Main()  
  {  
    const string FILE_PATTERN = "*.xlsx";  
  
    string excelFolder;  
    string[] excelFiles;  
  
    excelFolder = Dts.Variables["ExcelFolder"].Value.ToString();  
    excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN);  
  
    Dts.Variables["ExcelFiles"].Value = excelFiles;  
  
    Dts.TaskResult = (int)ScriptResults.Success;  
  }  
}

대체 솔루션

스크립트 태스크를 사용하여 Excel 파일의 목록을 배열로 수집하는 대신 ForEach File 열거자를 사용하여 폴더의 모든 Excel 파일을 반복할 수도 있습니다. 자세한 내용은 Foreach 루프 컨테이너를 사용하여 Excel 파일 및 테이블을 통한 루프를 참조 하세요.

예제 4 설명: Excel 파일에서 테이블 목록 가져오기

이 예에서는 ExcelFile 변수 값으로 지정된 Excel 통합 문서 파일에 있는 워크시트 및 명명된 범위의 목록으로 배열을 채운 다음 이 배열을 ExcelTables에 복사합니다. Foreach from Variable 열거자를 사용하여 배열의 테이블을 반복할 수 있습니다.

참고 항목

Excel 통합 문서의 테이블 목록에는 워크시트($ 접미사가 있는) 및 명명된 범위가 모두 포함됩니다. 워크시트만 포함하거나 명명된 범위 목록만 포함하도록 목록을 필터링해야 하는 경우에는 이를 위한 다른 코드를 추가해야 합니다.

이 스크립트 태스크 예제를 구성하려면

패키지에 새 스크립트 작업을 추가하고 해당 이름을 GetExcelTables로 변경합니다.
스크립트 태스크 편집기를 열고 스크립트 탭에서 ReadOnlyVariables를 클릭한 후 다음 방법 중 하나를 사용하여 속성 값을 입력합니다.
- ExcelFile을 입력 합니다.
  
  또는
- 속성 필드 옆의 줄임표(...) 단추를 클릭하고 변수 선택 대화 상자에서 ExcelFile 변수를 선택합니다.
ReadWriteVariables를 클릭하고 다음 방법 중 하나를 사용하여 속성 값을 입력합니다.
- ExcelTable을 입력 합니다.
  
  또는
- 속성 필드 옆의 줄임표(...) 단추를 클릭하고 변수 선택 대화 상자에서 ExcelTablesvariable을 선택합니다.
스크립트 편집을 클릭하여 스크립트 편집기를 엽니다.
스크립트 프로젝트에 System.Xml 네임스페이스에 대한 참조를 추가합니다.
스크립트 파일의 맨 위에 System.Data.OleDb 네임스페이스에 대한 Imports 문을 추가합니다.
다음 코드를 추가합니다.

예제 4 코드

Public Class ScriptMain  
  Public Sub Main()  
    Dim excelFile As String  
    Dim connectionString As String  
    Dim excelConnection As OleDbConnection  
    Dim tablesInFile As DataTable  
    Dim tableCount As Integer = 0  
    Dim tableInFile As DataRow  
    Dim currentTable As String  
    Dim tableIndex As Integer = 0  
  
    Dim excelTables As String()  
  
    excelFile = Dts.Variables("ExcelFile").Value.ToString  
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _  
        "Data Source=" & excelFile & _  
        ";Extended Properties=Excel 12.0"  
    excelConnection = New OleDbConnection(connectionString)  
    excelConnection.Open()  
    tablesInFile = excelConnection.GetSchema("Tables")  
    tableCount = tablesInFile.Rows.Count  
    ReDim excelTables(tableCount - 1)  
    For Each tableInFile In tablesInFile.Rows  
      currentTable = tableInFile.Item("TABLE_NAME").ToString  
      excelTables(tableIndex) = currentTable  
      tableIndex += 1  
    Next  
  
    Dts.Variables("ExcelTables").Value = excelTables  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class

public class ScriptMain  
{  
  public void Main()  
        {  
            string excelFile;  
            string connectionString;  
            OleDbConnection excelConnection;  
            DataTable tablesInFile;  
            int tableCount = 0;  
            string currentTable;  
            int tableIndex = 0;  
  
            string[] excelTables = new string[5];  
  
            excelFile = Dts.Variables["ExcelFile"].Value.ToString();  
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +  
                "Data Source=" + excelFile + ";Extended Properties=Excel 12.0";  
            excelConnection = new OleDbConnection(connectionString);  
            excelConnection.Open();  
            tablesInFile = excelConnection.GetSchema("Tables");  
            tableCount = tablesInFile.Rows.Count;  
  
            foreach (DataRow tableInFile in tablesInFile.Rows)  
            {  
                currentTable = tableInFile["TABLE_NAME"].ToString();  
                excelTables[tableIndex] = currentTable;  
                tableIndex += 1;  
            }  
  
            Dts.Variables["ExcelTables"].Value = excelTables;  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
        }  
}

대체 솔루션

스크립트 작업을 사용하여 Excel 테이블 목록을 배열로 수집하는 대신 ForEach ADO.NET 스키마 행 집합 열거자를 사용하여 Excel 통합 문서 파일의 모든 테이블(즉, 워크시트 및 명명된 범위)을 반복할 수도 있습니다. 자세한 내용은 Foreach 루프 컨테이너를 사용하여 Excel 파일 및 테이블을 통한 루프를 참조 하세요.

샘플 결과 표시

이 항목의 각 예제를 동일한 패키지에 구성한 경우 모든 스크립트 작업을 모든 예제의 출력을 표시하는 추가 스크립트 태스크에 연결할 수 있습니다.

이 항목의 예제 출력을 표시하도록 스크립트 작업을 구성하려면

패키지에 새 스크립트 작업을 추가하고 해당 이름을 DisplayResults로 변경합니다.
앞의 작업이 성공적으로 완료된 후 각 태스크가 실행되도록 네 가지 예제 스크립트 태스크를 각각 서로 연결하고 네 번째 예제 작업을 DisplayResults 작업에 연결합니다 .
스크립트 태스크 편집기에서 DisplayResults 작업을 엽니다.
스크립트 탭에서 ReadOnlyVariables를 클릭하고 다음 방법 중 하나를 사용하여 예제를 테스트하기 위한 패키지 구성에 나열된 7개의 변수를 모두 추가합니다.
- 각 변수의 이름을 쉼표로 구분하여 입력합니다.
  
  또는
- 속성 필드 옆의 줄임표(...) 단추를 클릭하고 변수 선택 대화 상자에서 변수 를 선택합니다.
스크립트 편집을 클릭하여 스크립트 편집기를 엽니다.
스크립트 파일의 맨 위에 Microsoft.VisualBasic 및 System.Windows.Forms 네임스페이스에 대한 Imports 문을 추가합니다.
다음 코드를 추가합니다.
패키지를 실행하고 메시지 상자에 표시된 결과를 확인합니다.

결과를 표시하는 코드

Public Class ScriptMain  
  Public Sub Main()  
    Const EOL As String = ControlChars.CrLf  
  
    Dim results As String  
    Dim filesInFolder As String()  
    Dim fileInFolder As String  
    Dim tablesInFile As String()  
    Dim tableInFile As String  
  
    results = _  
      "Final values of variables:" & EOL & _  
      "ExcelFile: " & Dts.Variables("ExcelFile").Value.ToString & EOL & _  
      "ExcelFileExists: " & Dts.Variables("ExcelFileExists").Value.ToString & EOL & _  
      "ExcelTable: " & Dts.Variables("ExcelTable").Value.ToString & EOL & _  
      "ExcelTableExists: " & Dts.Variables("ExcelTableExists").Value.ToString & EOL & _  
      "ExcelFolder: " & Dts.Variables("ExcelFolder").Value.ToString & EOL & _  
      EOL  
  
    results &= "Excel files in folder: " & EOL  
    filesInFolder = DirectCast(Dts.Variables("ExcelFiles").Value, String())  
    For Each fileInFolder In filesInFolder  
      results &= " " & fileInFolder & EOL  
    Next  
    results &= EOL  
  
    results &= "Excel tables in file: " & EOL  
    tablesInFile = DirectCast(Dts.Variables("ExcelTables").Value, String())  
    For Each tableInFile In tablesInFile  
      results &= " " & tableInFile & EOL  
    Next  
  
    MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information)  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class

public class ScriptMain  
{  
  public void Main()  
        {  
            const string EOL = "\r";  
  
            string results;  
            string[] filesInFolder;  
            //string fileInFolder;  
            string[] tablesInFile;  
            //string tableInFile;  
  
            results = "Final values of variables:" + EOL + "ExcelFile: " + Dts.Variables["ExcelFile"].Value.ToString() + EOL + "ExcelFileExists: " + Dts.Variables["ExcelFileExists"].Value.ToString() + EOL + "ExcelTable: " + Dts.Variables["ExcelTable"].Value.ToString() + EOL + "ExcelTableExists: " + Dts.Variables["ExcelTableExists"].Value.ToString() + EOL + "ExcelFolder: " + Dts.Variables["ExcelFolder"].Value.ToString() + EOL + EOL;  
  
            results += "Excel files in folder: " + EOL;  
            filesInFolder = (string[])(Dts.Variables["ExcelFiles"].Value);  
            foreach (string fileInFolder in filesInFolder)  
            {  
                results += " " + fileInFolder + EOL;  
            }  
            results += EOL;  
  
            results += "Excel tables in file: " + EOL;  
            tablesInFile = (string[])(Dts.Variables["ExcelTables"].Value);  
            foreach (string tableInFile in tablesInFile)  
            {  
                results += " " + tableInFile + EOL;  
            }  
  
            MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
        }  
}

참고 항목

SSIS(SQL Server Integration Services)를 사용하여 Excel에서 또는 Excel로 데이터 로드
Foreach 루프 컨테이너를 사용하여 Excel 파일 및 테이블 루핑

다음을 통해 공유

스크립트 태스크를 사용한 Excel 파일 작업

샘플을 테스트하도록 패키지 구성

패키지를 이 항목의 예에 사용할 수 있도록 구성하려면

예제 1 설명: Excel 파일이 있는지 확인

이 스크립트 태스크 예제를 구성하려면

예제 1 코드

예제 2 설명: Excel 테이블이 있는지 확인

이 스크립트 태스크 예제를 구성하려면

예제 2 코드

예제 3 설명: 폴더에서 Excel 파일 목록 가져오기

이 스크립트 태스크 예제를 구성하려면

예 3 코드

대체 솔루션

예제 4 설명: Excel 파일에서 테이블 목록 가져오기

이 스크립트 태스크 예제를 구성하려면

예제 4 코드

대체 솔루션

샘플 결과 표시

이 항목의 예제 출력을 표시하도록 스크립트 작업을 구성하려면

결과를 표시하는 코드

참고 항목

피드백

추가 리소스