• 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