Excel tab name driven by a variable

  • 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?

  • 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?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.

  • 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