Load data from Dynamic file name and Dynamic Sheet name Excel source using ACE.OLEDB in SSIS

If the Excel sheet name is dynamic, you might find it difficult to automate using SSIS. This post covers using Provider=Microsoft.ACE.OLEDB.12.0.  ACE has superseded JET. There are many posts using Provider=Microsoft.Jet.OLEDB.4.0, however the Jet engine can only access office 97-2003, but you cannot access office 2007 and later versions. In addition, ACE provides a 64-bit driver, so can be used on 64-bit machines, whereas JET cannot.

SSIS Script Task C# code

Add variables one for Excel full file path and other one for store dynamic sheet name

//add these 2 namespaces

using System.IO;

using System.Data.OleDb;

// TODO: Add your user Main()

public void Main()

{

            

            string fileToTest = Dts.Variables["User::VarExcelFile"].Value.ToString();

            string ConStr;

            string HDR;

            HDR = "YES";

            ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileToTest + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";

            OleDbConnection cnn = new OleDbConnection(ConStr);

            cnn.Open();

            DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            foreach (DataRow drSheet in dtSheet.Rows)

            {

                Dts.Variables["User::VarSheetName"].Value = drSheet["TABLE_NAME"].ToString();

                break;

            }

            MessageBox.Show(Dts.Variables["User::VarSheetName"].Value.ToString());

            Dts.TaskResult = (int)ScriptResults.Success;

}

In this case, I am interested in only one sheet, so I used break inside the foreach loop, if you are interested with multiple sheets, just add the logic under foreach.

If the excel file name is also dynamic, then you can use the following command to find the latest file name using Script Task.

public void Main()

{

            // TODO: Add your code here

            var directory = new DirectoryInfo(Dts.Variables["User::VarFolderPath"].Value.ToString());

            FileInfo[] files = directory.GetFiles();

            DateTime lastModified = DateTime.MinValue;

            foreach (FileInfo file in files)

            {

                if (file.LastWriteTime > lastModified)

                {

                    lastModified = file.LastWriteTime;

                    Dts.Variables["User::VarFileName"].Value = file.ToString();                                   

                }

            }

            string filepath = Dts.Variables["User::VarFolderPath"].Value.ToString() + "\\" + Dts.Variables["User::VarFileName"].Value.ToString();

            Dts.TaskResult = (int)ScriptResults.Success;

}

Cheers!
Uma