Sunday, February 12, 2012

Data source name not found and no default driver specified at System.Data.Odbc.OdbcConnection.HandleError

Started: 1:03:12 PM Progress: 2012-02-08 13:03:15.24 Source: Data Flow Task Validating: 0% complete End Progress Error: 2012-02-08 13:03:16.15 Code: 0xC0047062 Source: Data Flow Task V91MOD APIMAG [1] Description: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)

DSN data sources does not appear in the DSN drop down menu in connection manager.

Recently while creating a SSIS package I had to connect to IBM iSeries AS400 servers and I had asked someone to install the ODBC drivers. So they had installed the IBM iSeries Access for Windows and created a SYSTEM DSN for me to use. I was using the connection string like the following.

DSN=mySystemDSN; user=myuser; PWD=mypass;

But I was getting the above mentioned error. After researching for some time I found that BIDS is a 32 bit application and installed version of SQL Server was 64 bit application and hence the issue. I could not even run the package in debug mode. The recommendation was to change a property in 'Solution's properties'. Change the property called 'Run64BitRunTime' to FALSE.

Also you need to enable the 32 bit runtime in tab 'Execution options' in the SQL Server Job step that will run the SSIS package.

But in spite of all this I still had issues running the package in both the debug mode and the SQL Server job step.
One more issue that I found was you could not pick the DSN in the drop down menu of the connection manager. If you pick the option ADO.NET provider (.Net Providers\ODBC data provider) then you won't find the ODBC DSN data source that you created.

The trick is to create both the 32 bit and the 64 bit DSN using the ODBC administrator. For this go to Program menu and then IBM iSeries Access for Windows and create the system DSN under both 'ODBC Administrator' and the 'ODBC Administrator 64 bit'.

This way while editing the package you will find the DSN in the connection manger. This will be the 32 bit DSN and when you run the package it will also find the 64 DSN if required.