Import Excel records in SQL Server 2016 via VS2017 SSIS / Error DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

Question

Good day !

OK so I've made researches to find a solution to my problem, and tried everything and still...

I have to upload records from Excel to SQL Server. First I created a SQL Connection Manager, with the dynamic variable @[User::FilePath] as an expression, and it was working perfectly... until this morning !

Error message:

Error: 0xC0202009 at CriticalList, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "External table is not in the expected format.".

Error: 0xC020801C at Load Input file into Staging table, Excel Source 1: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error: 0xC0047017 at Load Input file into Staging table, SSIS.Pipeline: Excel Source failed validation and returned error code 0xC020801C.

Here is how I setup my connection:

Since, I tried to :

Set the ConnectionString as an expression instead of the FilePath as follow:

“Provider=Microsoft.ACE.OLEDB.12.0; User ID=;Data Source=” + @[User::FilePath] + “;Extended Properties="EXCEL 12.0;HDR=YES;IMEX=1";”

Create an OLE DB Connection instead (Provider=Microsoft.ACE.OLEDB.12.0), but I didn't find the way to pass the FilePath.

Please note that the account that I am using has access to all of the directories where the files are located. Can somebody help me with this please ? My deadline is in two weeks, and I am stuck with this :-(

Thanks a lot in advance for your help.

Mylene

Answer 1

I updated the package to use bulk insert (ADO Connection) in a script task. It' working perfectly. Here is the CSharp code:

    public void Main()
    {
        string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
        try
        {
            //Declare Variables
            
            string ExcelFileName = Dts.Variables["$Package::ExcelFileName"].Value.ToString();
            string FolderPath = Dts.Variables["$Package::FolderPath"].Value.ToString();
            string TableName = Dts.Variables["$Package::SQLTableName"].Value.ToString();
            string SchemaName = Dts.Variables["$Package::SQLTableSchema"].Value.ToString();
            string SheetName = Dts.Variables["$Package::SheetName"].Value.ToString();
            ExcelFileName = ExcelFileName + "_" + datetime;
            string lastChar = FolderPath.Substring(FolderPath.Length - 1);

            

            //Validate format of FolderPath
            if (lastChar != "\\")
            {
                FolderPath = FolderPath + "\\";
            }


            OleDbConnection Excel_OLE_Con = new OleDbConnection();
            OleDbCommand Excel_OLE_Cmd = new OleDbCommand();

            //Construct ConnectionString for Excel
            string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FolderPath + ExcelFileName
                + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";

            //drop Excel file if exists
            File.Delete(FolderPath + "\\" + ExcelFileName + ".xlsx");

            //USE ADO.NET Connection from SSIS Package to get data from table
            SqlConnection myADONETConnection = new SqlConnection();
            myADONETConnection = (SqlConnection)(Dts.Connections["ADO_DBConnection"].AcquireConnection(Dts.Transaction) as SqlConnection);
                            
            //Load Data into DataTable from SQL ServerTable
            // Assumes that connection is a valid SqlConnection object.
            string queryString =
              "SELECT * from " + SchemaName + "." + TableName;
            SqlDataAdapter adapter = new SqlDataAdapter(queryString, myADONETConnection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);


            //Get Header Columns
            string TableColumns = "";

            // Get the Column List from Data Table so can create Excel Sheet with Header
            foreach (DataTable table in ds.Tables)
            {
                foreach (DataColumn column in table.Columns)
                {
                    TableColumns += column + "],[";
                }
            }

            // Replace most right comma from Columnlist
            TableColumns = ("[" + TableColumns.Replace(",", " Text,").TrimEnd(','));
            TableColumns = TableColumns.Remove(TableColumns.Length - 2);
            


            //Use OLE DB Connection and Create Excel Sheet
            Excel_OLE_Con.ConnectionString = connstring;
            Excel_OLE_Con.Open();
            Excel_OLE_Cmd.Connection = Excel_OLE_Con;
            Excel_OLE_Cmd.CommandText = "Create table " + SheetName + " (" + TableColumns + ")";
            Excel_OLE_Cmd.ExecuteNonQuery();


            //Write Data to Excel Sheet from DataTable dynamically
            foreach (DataTable table in ds.Tables)
            {
                String sqlCommandInsert = "";
                String sqlCommandValue = "";
                foreach (DataColumn dataColumn in table.Columns)
                {
                    sqlCommandValue += dataColumn + "],[";
                }

                sqlCommandValue = "[" + sqlCommandValue.TrimEnd(',');
                sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2);
                sqlCommandInsert = "INSERT into " + SheetName + "(" + sqlCommandValue.TrimEnd(',') + ") VALUES(";

                int columnCount = table.Columns.Count;
                foreach (DataRow row in table.Rows)
                {
                    string columnvalues = "";
                    for (int i = 0; i < columnCount; i++)
                    {
                        int index = table.Rows.IndexOf(row);
                        columnvalues += "'" + table.Rows[index].ItemArray[i] + "',";

                    }
                    columnvalues = columnvalues.TrimEnd(',');
                    var command = sqlCommandInsert + columnvalues + ")";
                    Excel_OLE_Cmd.CommandText = command;
                    Excel_OLE_Cmd.ExecuteNonQuery();
                }

            }
            Excel_OLE_Con.Close();
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        catch (Exception exception)
        {

            // Create Log File for Errors
            using (StreamWriter sw = File.CreateText(Dts.Variables["$Package::FolderPath"].Value.ToString() + "\\" + Dts.Variables["$Package::ExcelFileName"].Value.ToString() + datetime + ".log"))
            {
                sw.WriteLine(exception.ToString());
                Dts.TaskResult = (int)ScriptResults.Failure;

            }
        }
    }

}

}