January 22, 2016 at 1:49 pm
I have SSIS package created in 2005v for loading excel source data to OLE DB destination. In my Data Flow Task I have Excel as source. I receive excel monthly however the client changes the tab/sheet name each time. How do I create variable for this so I do not have to rename tab/sheet before loading?
January 22, 2016 at 2:06 pm
TAman (1/22/2016)
I have SSIS package created in 2005v for loading excel source data to OLE DB destination. In my Data Flow Task I have Excel as source. I receive excel monthly however the client changes the tab/sheet name each time. How do I create variable for this so I do not have to rename tab/sheet before loading?
Creating variables is explained here[/url]. Is that all that you wanted to know?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 22, 2016 at 2:36 pm
No. I understand variables. That was not my question. I'll try to break it down deeper. My question is how to load an excel that contains sheet$ that is different every time. Right now, my connection manager is hardcoded using static name for sheet$. I have no create variable before to look for first sheet regardless of name. I have Foreach Loop Container configured with variable to pick up excel file YourFile*_*.xls but within the file contains a sheet$ that never consistently named same. When person sends me YourFile*_*.xls and I try to load, it fails because main sheet$ is named different. Hope this helps you help me.
January 23, 2016 at 9:03 am
TAman (1/22/2016)
No. I understand variables. That was not my question. I'll try to break it down deeper. My question is how to load an excel that contains sheet$ that is different every time. Right now, my connection manager is hardcoded using static name for sheet$. I have no create variable before to look for first sheet regardless of name. I have Foreach Loop Container configured with variable to pick up excel file YourFile*_*.xls but within the file contains a sheet$ that never consistently named same. When person sends me YourFile*_*.xls and I try to load, it fails because main sheet$ is named different. Hope this helps you help me.
As a precursor to your Data Flow Task you can use a C# Script Task to leaf through the Workbook and find the name of the first Worksheet. Once you have identified the name of the first Worksheet you can store the name in a variable and reference it in the OLE DB Source inside your Data Flow Task to select data from that sheet. Here is the C# code if you want to try it out.
ublic void Main()
{
// store file name passed into Script Task
string WorkbookFileName = Dts.Variables["User::WorkbookFileName"].Value.ToString();
// setup connection string
string connStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"EXCEL 12.0;HDR=Yes;IMEX=1;\"", WorkbookFileName);
// setup connection to Workbook
using (var conn = new OleDbConnection(connStr))
{
try
{
// connect to Workbook
conn.Open();
// get Workbook schema
using (DataTable worksheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
{
// in ReadWrite variable passed into Script Task, store third column in the first
// row of the DataTable which contains the name of the first Worksheet
Dts.Variables["User::WorksheetOneName"].Value = worksheets.Rows[0][2].ToString();
}
}
catch (Exception)
{
throw;
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply