CODEWITHSUNDEEP
excelwpfssisodbcoledb
Rajesh
Rajesh

Reputation: 31

Data source name not found and no default driver specified". Failed to acquire connection "DestinationConnectionOLEDB"

I have created a SSIS package to import the excel to sql. I have set the connection string and excel file path by expression.

After that I'm trying to invoke package by c# WPF application by

Microsoft.SqlServer.Dts.Runtime.DTSExecResult and passing value of connection string and excel path. My code is as shown below

Application app = new Application();
Package package = null;
//Load the SSIS Package which will be executed
package = app.LoadPackage("D:\\EMS-Docs\\new\\SSIS\\SSISProject\\obj\\Development\\Package2.dtsx", null);
//Pass the varibles into SSIS Package

//Microsoft.SqlServer.Dts.Runtime.Variables myVars = package.Variables;

package.Variables["User::EXLPath"].Value = "D:\\EMS-Docs\\SSIS\\PRACTICAL_1901_LOT-2.xls";
package.Variables["User::SQLConn"].Value = GlobalClass.TransactionalDBConnectionString;


//Execute the SSIS Package and store the Execution Result
Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();

The error

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". Failed to acquire connection "DestinationConnectionOLEDB". Connection may not be configured correctly or you may not have the right permissions on this connection.

Upvotes: 2

Views: 3482

Answers (1)

Hadi
Hadi

Reputation: 37313

Error cause

The main error is:

"[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

Which means that the main problem is in the connection manager or the connection string.

Possible solutions

  1. Make sure that you have set the connection manager and Data Flow Tasks Delay Validation property to True
  2. Make sure that you are passing a valid connection string value by executing the package from visual studio with the same variable value.
  3. Make sure that the application mode (32bit/64bit) is identical with the ODBC driver version. As example: if you have 32-bit ODBC driver installed, make sure that you are running the application in 32-bit mode
  4. If you are connecting to SQL Server you can use SQL Native Client or Microsoft OLEDB Provider for SQL Server instead of ODBC provider

Additional Information

Also, you can refer to the following knowledge base article for more information about the error possible causes / solutions:

Upvotes: 1

Related Questions