스크립트 태스크를 사용한 Excel 파일 작업
Integration Services에서는 Microsoft Excel 파일 형식으로 스프레드시트에 저장된 데이터를 작업하기 위한 Excel 연결 관리자, Excel 원본 및 Excel 대상을 제공합니다. 이 항목에서는 스크립트 태스크를 사용하여 사용 가능한 Excel 데이터베이스(통합 문서 파일) 및 테이블(워크시트 및 명명된 범위)에 대한 정보를 가져오는 기술을 설명합니다. 이러한 예제는 Microsoft Jet OLE DB 공급자가 지원하는 다른 파일 기반 데이터 원본에도 사용할 수 있도록 쉽게 수정할 수 있습니다.
예제를 테스트하기 위한 패키지 구성
예 1: Excel 파일의 존재 여부 확인
예 2: Excel 테이블의 존재 여부 확인
예 3: 폴더의 Excel 파일 목록 가져오기
예 4: Excel 파일의 테이블 목록 가져오기
예제 결과 표시
[!참고]
여러 패키지에서 쉽게 다시 사용할 수 있는 태스크를 만들려면 이 스크립트 태스크 예제에 있는 코드를 바탕으로 사용자 지정 태스크를 만들어 보십시오. 자세한 내용은 사용자 지정 태스크 개발을 참조하십시오.
예제를 테스트하기 위한 패키지 구성
단일 패키지에서 이 항목의 모든 예제를 테스트할 수 있도록 구성할 수 있습니다. 이 항목의 예제에서는 대개 동일한 여러 패키지 변수와 동일한 .NET Framework 클래스를 사용합니다.
패키지를 이 항목의 예에 사용할 수 있도록 구성하려면
Business Intelligence Development Studio에서 새 Integration Services 프로젝트를 만들고 편집을 위해 기본 패키지를 엽니다.
변수. 변수 창을 열고 다음 변수를 정의합니다.
String 형식의 ExcelFile. 기존 Excel 통합 문서의 전체 경로와 파일 이름을 입력합니다.
String 형식의 ExcelTable. 기존 워크시트의 이름이나 ExcelFile 변수의 값에 명명된 통합 문서의 명명된 범위를 입력합니다. 이 값은 대/소문자를 구분합니다.
Boolean 형식의 ExcelFileExists
Boolean 형식의 ExcelTableExists
String 형식의 ExcelFolder. 적어도 하나의 Excel 통합 문서가 들어 있는 폴더의 전체 경로를 입력합니다.
Object 형식의 ExcelFiles
Object 형식의 ExcelTables
문 가져오기. 대부분의 코드 예제에서는 스크립트 파일의 맨 위에서 다음 .NET Framework 네임스페이스 중 하나 또는 둘 모두를 가져와야 합니다.
System.IO - 파일 시스템 작업의 경우
System.Data.OleDb - Excel 파일을 데이터 원본으로 열려는 경우
참조. 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 테이블의 존재 여부 확인
이 예에서는 ExcelTable 변수에 지정된 Excel 워크시트 또는 명명된 범위가 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.Jet.OLEDB.4.0;" & _
"Data Source=" & fileToTest & _
";Extended Properties=Excel 8.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.Jet.OLEDB.4.0;" +
"Data Source=" + fileToTest + ";Extended Properties=Excel 8.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 from Variable 열거자를 사용하여 배열의 파일을 반복할 수 있습니다.
이 스크립트 태스크 예를 구성하려면
패키지에 새 스크립트 태스크를 추가하고 해당 이름을 GetExcelFiles로 바꿉니다.
스크립트 태스크 편집기를 열고 스크립트 탭에서 ReadOnlyVariables를 클릭한 후 다음 방법 중 하나를 사용하여 속성 값을 입력합니다.
ExcelFolder를 입력합니다.
-또는-
속성 필드 옆의 줄임표() 단추를 클릭하고 변수 선택 대화 상자에서 ExcelFolder 변수를 선택합니다.
ReadWriteVariables를 클릭하고 다음 방법 중 하나를 사용하여 속성 값을 입력합니다.
ExcelFiles를 입력합니다.
-또는-
속성 필드 옆의 줄임표() 단추를 클릭하고 변수 선택 대화 상자에서 ExcelFiles 변수를 선택합니다.
스크립트 편집을 클릭하여 스크립트 편집기를 엽니다.
System.IO 네임스페이스에 대한 Imports 문을 스크립트 파일의 맨 위에 추가합니다.
다음 코드를 추가합니다.
예 3 코드
Public Class ScriptMain
Public Sub Main()
Const FILE_PATTERN As String = "*.xls"
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 = "*.xls";
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를 클릭하고 다음 방법 중 하나를 사용하여 속성 값을 입력합니다.
ExcelTables를 입력합니다.
-또는-
속성 필드 옆의 줄임표() 단추를 클릭하고 변수 선택 대화 상자에서 ExcelTables 변수를 선택합니다.
스크립트 편집을 클릭하여 스크립트 편집기를 엽니다.
스크립트 프로젝트에 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.Jet.OLEDB.4.0;" & _
"Data Source=" & excelFile & _
";Extended Properties=Excel 8.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.Jet.OLEDB.4.0;" +
"Data Source=" + excelFile + ";Extended Properties=Excel 8.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;
}
}
|
.gif "Integration Services 아이콘(작은 아이콘)")
Integration Services 관련 최신 정보 얻기