Case
I have an Excel file with multiple identical worksheets (one foreach month) and I want to add the data to a single database table. Is there a foreach loop solution so that I don't need to add multiple data flows or sources.?
Solution
a) You could use a union all query in the Excel Source. Not very flexible, but very easy.
SELECT Column1
, Column2
, Column3
FROM [Sheet1$]
UNION ALL
SELECT Column1
, Column2
, Column3
FROM [Sheet2$]
UNION ALL
SELECT Column1
, Column2
, Column3
FROM [Sheet3$]
b) You could loop through Excel tables by using the
Foreach ADO.NET Schema Rowset enumerator. One downside: it returns both worksheets (which have the $ suffix) and named ranges. So you need an extra dummy task in your foreach loop with an expression on the precedence constraint to the next task. Something like: RIGHT(@[User::WorksheetName], 1) == "$"
c) You could use a Script Task to fill a SSIS object variable and use that to loop through. Let's elaborate that solution.
*update: Custom
Excel Worksheet Enumerator.
1) Excel Connection Manager and Variables
Add an Excel Connection manager with a link to an Excel File with multiple Worksheets. I named mine "MyExcelFile". If you choose an other name, then make sure to also change that in the script task of step 3.
And we also need two SSIS variables. One object variable (ExcelWorksheets) to loop through in the foreach loop and one string variable (WorksheetName) to be filled by the foreach loop.
 |
| Two variables |
2) Script Task
Add a Script Task to your Control Flow and give it a suitable name. Edit the Script Task and add the SSIS Object variable "ExcelWorksheets" from step 1 as ReadWrite variable.
 |
| ReadWriteVariables |
3) The script
Edit the Script Task (open VSTA editor) and copy the following script.
// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb; // Added
namespace ST_0607edbf2c834733a551cd01039cb715.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
try
{
// Get connectionstring from Excel Connection Manager and use it to connect through OLE DB
OleDbConnection excelConnection = new OleDbConnection(Dts.Connections["MyExcelFile"].ConnectionString);
excelConnection.Open();
// Once connected, get the table schema and close the OLE DB connection
DataTable dtDatasetsInExcel = excelConnection.GetSchema("Tables");
excelConnection.Close();
// NOTE: The datatable from GetSchema includes both worksheets (which have the $ suffix)
// and named ranges. So we need to exclude those named ranges.
// Create a dataset.
DataSet dsWorksheetsInExcel = new DataSet();
// Create a new table in the dataset
DataTable dtWorksheetsInExcel = dsWorksheetsInExcel.Tables.Add();
dtWorksheetsInExcel.Columns.Add("WorksheetName", typeof(string));
// Loop through all tables and only get those ending with a $
foreach (DataRow drWorksheet in dtDatasetsInExcel.Rows)
{
// Check for $ suffix
if (drWorksheet["TABLE_NAME"].ToString().EndsWith("$"))
{
dtWorksheetsInExcel.Rows.Add(drWorksheet["TABLE_NAME"].ToString());
}
}
// Fire information event with the total number of worksheets
bool fireAgain = true;
Dts.Events.FireInformation(-1, "Foreach Worksheet", dtWorksheetsInExcel.Rows.Count.ToString() + " worksheets found.", string.Empty, 0, ref fireAgain);
// Fill SSIS Object variable with worksheet dataset.
Dts.Variables["User::ExcelWorksheets"].Value = dsWorksheetsInExcel;
// Success
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
// Something went wrong. Log error and fail Script Task
Dts.Events.FireError(-1,"Foreach Worksheet", ex.Message, string.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
}
or VB.Net
' VB.Net code
Imports System
Imports System.Data
Imports System.Data.OleDb ' Added
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="" > _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Try
' Get connectionstring from Excel Connection Manager and use it to connect through OLE DB
Dim excelConnection As New OleDbConnection(Dts.Connections("MyExcelFile").ConnectionString)
excelConnection.Open()
' Once connected, get the table schema and close the OLE DB connection
Dim dtDatasetsInExcel As DataTable = excelConnection.GetSchema("Tables")
excelConnection.Close()
' NOTE: The datatable from GetSchema includes both worksheets (which have the $ suffix)
' and named ranges. So we need to exclude those named ranges.
' Create a dataset.
Dim dsWorksheetsInExcel As New DataSet()
' Create a new table in the dataset
Dim dtWorksheetsInExcel As DataTable = dsWorksheetsInExcel.Tables.Add()
dtWorksheetsInExcel.Columns.Add("WorksheetName", GetType(String))
' Loop through all tables and only get those ending with a $
For Each drWorksheet As DataRow In dtDatasetsInExcel.Rows
' Check for $ suffix
If drWorksheet("TABLE_NAME").ToString().EndsWith("$") Then
dtWorksheetsInExcel.Rows.Add(drWorksheet("TABLE_NAME").ToString())
End If
Next
' Fire information event with the total number of worksheets
Dim fireAgain As Boolean = True
Dts.Events.FireInformation(-1, "Foreach Worksheet", dtWorksheetsInExcel.Rows.Count.ToString() & " worksheets found.", String.Empty, 0, fireAgain)
' Fill SSIS Object variable with worksheet dataset.
Dts.Variables("User::ExcelWorksheets").Value = dsWorksheetsInExcel
' Success
Dts.TaskResult = ScriptResults.Success
Catch ex As Exception
' Something went wrong. Log error and fail Script Task
Dts.Events.FireError(-1, "Foreach Worksheet", ex.Message, String.Empty, 0)
Dts.TaskResult = ScriptResults.Failure
End Try
End Sub
End Class
 |
| Worksheets and named ranges |
4) Foreach Loop
Add a Foreach Loop Container to the Control Flow and give it a suitable name. Connect it to the Script Task from step 2.
 |
| Foreach Loop |
5) Foreach ADO enumerator
Edit the Foreach Loop Container and select the ADO enumerator in the collection tab. After that select the SSIS object variable from step 1 as ADO object source variable and select Rows in first table as the enumeration mode.
 |
| Foreach ADO enumerator |
6) Variable mappings
In the Variable mappings tab select the string variable from step 1. Now the Foreach Loop will fill this variable with the currect Worksheet name.
 |
| Variable mappings |
7) Excel Source
Add a Data Flow Task in the foreach loop and add a Excel Source to that Data Flow Task. Select the Excel Connection manager created in step 1 and select
Table name or view name variable. Now you can select the variable that contains the Worksheet name.
 |
| Excel Source using variable for table name |
Note 1: hidden worksheets are not returned by the OLE DB provider.
Note 2: Worksheets are always sorted ascending on their name
Note 3: If you have different data structures, but worksheets all have some common columns, then you could create a query in a variable and use that as a source query. Something like "SELECT column1, column4 FROM " + @[User::WorksheetName]
