February 13, 2013 at 9:34 am
Help!
I have an SSIS package with multiple data flow tasks. Each of these uses an ODBC data provider.
When I run the package in Visual Studio, everything works fine.
When I set up the package to run as a SQL Server agent job, I get an ODBC Exception:
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) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions) at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) ... The package execution fa... The step failed.
Now here's the strange part. This failure always occurs on the second data flow task. So it seems as if the first data flow task can connect, but the second data flow task cannot. If I disable the second data flow task in the control flow, the second enabled data flow task fails. So it's always failing after the first connection is made.
I've tried various solutions:
1. I made sure the ODBC data source is set up as a System DSN.
2. I validated the connection in Visual Studio again and again. Once again, everything runs fine in Visual Studio.
3. I put in the password in the data source connection string in the job step, thinking it might have something to do with the password.
4. I set the Connection Manager "RetainSameConnection" property to true, thinking something might not be able to reconnect.
I need to get this up and running yesterday. So any suggestions would be appreciated.
February 13, 2013 at 10:45 am
Looks like I solved my own problem.
The issue really has to do with using a 32 bit DLL (the ODBC driver in this case) and executing a 64 bit DTExec utility (the default with SQL Sever agent). I've had this issue before when using Excel as part of a SSIS package. But I didn't think I'd have the same problem with the ODBC data source.
The resolution is a bit convoluted. You have to run the job step as "Operating System (CmdExec)" and fill in the command prompt. In the command prompt, you have to point to the 32 bit DTExec utility so you don't use the default 64 bit utility. Add to that the fact that you have to provide the ODBC data source username and password in the command prompt.
The end result command prompt looks something like this:
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "D:\My SSIS Package Location.dtsx" /DECRYPT "MyPackagePassword" /CONNECTION "My ODBC DataSource;uid=MyODBCUserID;password=MyODBCPassword;Dsn=MyODBCDsn;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI
Ugly? Yep. But it works.
February 13, 2013 at 11:53 am
You could also try reverting it back to an SSIS Step Type and on the Execution Options Tab checking 'Use 32 bit runtime.' The requirement to pass the uid and pwd of the ODBC DSN on the command line is new to me so this technique may not work, but it might be worth a shot to maybe keep things simple and a bit more consistent, something I strive for in my environments.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 13, 2013 at 11:58 am
... except I'm using Sql Server 2005, and I don't believe it has the Use 32-bit Runtime option when setting up a Sql Server agent job step. I think that started to be available with SQL Sever 2008. So I had to jump through a few more hoops.
February 13, 2013 at 12:04 pm
Ahh, missed which Forum we were in. Cheers.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 30, 2017 at 9:33 am
I have to use this conection string DRIVER={PostgreSQL ANSI};Dsn=PostgreSQL30;uid=usr;pwd=pass;SERVER=appserver;DATABASE=database; and also Execution Options Tab checking 'Use 32 bit runtime.'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply