How to load multiple sheet of excel(2016) file in ssis

Question

This code perfectly work on my machine (I have excel 2010) but when my supervisor tried to run but not work on his machine(he have excel 2016) so for excel 2016 do i need to change connection ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";??

 string FolderPath = Dts.Variables["User::FolderPath"].Value.ToString();
        string TableName = Dts.Variables["User::TableName"].Value.ToString();
        string SchemaName = Dts.Variables["User::SchemaName"].Value.ToString();
        string SheetNameToLoad = Dts.Variables["User::SheetNameLike"].Value.ToString();

        var directory = new DirectoryInfo(FolderPath);
        FileInfo[] files = directory.GetFiles();

        //Declare and initilize variables
        string fileFullPath = "";


        SqlConnection myADONETConnection = new SqlConnection();
        myADONETConnection = (SqlConnection)(Dts.Connections["DBconnection"].AcquireConnection(Dts.Transaction) as SqlConnection);

        ////Get one Book(Excel file at a time)
        foreach (FileInfo file in files)
        {
            fileFullPath = FolderPath + "\\" + file.Name;
            MessageBox.Show(fileFullPath);

            //    //Create Excel Connection
            string ConStr;
            string HDR;
            HDR = "YES";
            ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
            OleDbConnection cnn = new OleDbConnection(ConStr);

        //    //Get Sheet Name
            cnn.Open();
            DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            string sheetname;
            sheetname = "";
            //Only read data from provided SheetNumber

            foreach (DataRow drSheet in dtSheet.Rows)
            {



                sheetname = drSheet["TABLE_NAME"].ToString();
                MessageBox.Show(sheetname);

                //Load the Data if Sheet Name contains value of SheetNameLike 
                if (sheetname.Contains(SheetNameToLoad) == true)
                {



                    //Load the DataTable with Sheet Data so we can get the column header
                    OleDbCommand oconn = new OleDbCommand("select  * from [" + sheetname + "] where CityName ='ARLINGTON'", cnn);
                    OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
                    DataTable dt = new DataTable();
                    adp.Fill(dt);
                    cnn.Close();



                    //Load Data from DataTable to SQL Server Table.
                    using (SqlBulkCopy BC = new SqlBulkCopy(myADONETConnection))
                    {
                        BC.DestinationTableName = SchemaName + "." + TableName;

                        BC.WriteToServer(dt);
                    }

                }
            }

LONG LONG · Accepted Answer · 2017-09-07T17:23:47

Did he get the error says ACE provider is not registered? If so, your supervisor need to download and install this on his machine:

https://www.microsoft.com/en-us/download/details.aspx?id=13255

How to load multiple sheet of excel(2016) file in ssis

1 Answers