Dynamic Sheet Name in SSIS Excel Spreadsheet imports

Scenario

I had to import excel data (just one sheet) into SQL database. The excel spreadsheet had multiple sheets where I am interested in only one sheet.

Solution

a) Create a new SSIS variable named "SheetName"

b) Create a script Task just before the Excel Source Task.

c) Add the following code to the main method of the script task

string excelFile = null;
string connectionString = null;
OleDbConnection excelConnection = null;
DataTable tablesInFile = null;
int tableCount = 0;
DataRow tableInFile = null;
string currentTable = null;
int tableIndex = 0;
string[] excelTables = null;

excelFile = Dts.Variables["User::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;
excelTables = new string[tableCount];

foreach (DataRow tableInFile_loopVariable in tablesInFile.Rows)
{
tableInFile = tableInFile_loopVariable;
currentTable = tableInFile["TABLE_NAME"].ToString();
excelTables[tableIndex] = currentTable;
tableIndex += 1;
}
}

Dts.Variables["User::SheetName"].Value = excelTables[0];

Dts.TaskResult = (int)ScriptResults.Success;


d) In your Excel source select the "Data access mode" as "Table name or view name variable".

e) Select the "User::SheetName" variable from the "Variable Name" dropdown list.

The connection "{XXX}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Scenario

I had a few connections created in the Connection Managers in SSIS and all these connection configurations are stored in the Package Configurations. I just deleted a few unwanted connections from the Connection Managers and I got the following error with the package for every single deleted connection.

Error loading XXX.dtsx: The connection "{XXX}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Solution

Update your package configurations to reflect the changes made to the Connection Managers and the errors will be cleared automatically.

Get Time difference using C#

DateTime startTime, endTime;

startTime = Convert.ToDateTime("2:30 AM");

endTime = Convert.ToDateTime("3:30 AM");

var timeDiff = new TimeSpan(endTime.Ticks - startTime.Ticks);

MessageBox.Show("Time difference in hours is " + timeDiff.Hours);

SSIS: Column cannot convert between unicode and non-unicode string data types

Scenario
If you try to import Excel data into SQL Server you may get this error having to convert the columns from Unicode to non-Unicode.

Solution

Use the Data Conversion Task
So to get around this problem we have to also use a Data Conversion task. This will allow us to convert data types so we can get the import completed. Map the non-unicode columns with unicode version of the columns and import the data to SQL server.

SSIS: Foreach File Enumerator in Foreach Loop Container with dynamic folder path using variables

Setting the folder path in foreach loop container dynamically using a variable.

This can be done by setting the Directory property of the Foreach File Enumerator.

1. Create two variables named @FolderPath and @FileName.

2. Drag a Foreach Loop Container task to the Control Flow panel, double click it to pop up the property window.

3. Switch to the Collection tab, choose the Enumerator as "Foreach File Enumerator", expand Expressions and add two properties "Directory" for folder path and "FileSpec" for filtering specific types of files. Specify "@[User::FolderPath]" to Directory and "*.txt" to FileSpec.

4. Switch to the Variable Mappings and specify User::FileName mapping to the index 0.