Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 SSIS ODBC Connection Manager Issue

Author  Topic 

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-21 : 13:29:50
I'm stumped on this and hope that someone has seen it before or has some suggestions. I have an SSIS package that pulls call information from our phone system which has an INFORMIX ODBC connection to it. Currently the DTSX that is running in an agent job in production is working, but when I exported it to make changes to it, the connection manager has the following error in Visual Studio. Google searches haven't found anything that matches what I'm experiencing.

Error 1 Validation error. Move call data from PHONESYSTEM to SQLSERVER: Move call data from PHONESYSTEM to SQLSERVER: System.Data.Odbc.OdbcException: ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [IM009] [Informix][Informix ODBC Driver]Unable to load translation shared library (DLL). ERROR [IM009] [Informix][Informix ODBC Driver]Unable to load translation shared library (DLL). 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) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.Odbc.OdbcConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction) Reporting_Call_Recs_SSIS.dtsx 0 0

I have tested the ODBC connection in the System DSN and it works just fine, but for some reason no matter what I try the connection manager still shows the above error and fails a connection test.

Any help would be greatly appreciated.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-21 : 13:54:25
1. If you need to make changes to it, then you can do it without modifying the package. In the job step, you can override the server/connection info in one of the tabs.
2. It sounds like a driver issue on the client machine. Try making the changes on the database server itself instead, though I realize Visual Studio might not be installed there. But it might be your only solution if the change you are making is more than just a connection string change.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-21 : 14:28:34
1) What I need to do is to modify the package to pull more columns from the phone system and put them into the destination table.
2) I am working in Visual Studio on the SQL Server that the agent job is running on. That's why this is so puzzling. I have even tried to remote into the server using the agent credentials but that didn't work either.

Could there be something that Visual Studio needs that the DTSX wouldn't need when run under the agent?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-21 : 14:33:47
I think you're going to need to call Informix for guidance on the error their driver is throwing. But alternatively, I would setup a test machine with Visual Studio and install the Informix driver there. See if you can load and make changes to the package there. If you can, you can then do a Save As to put it on the prod server.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-21 : 14:39:21
Thanks for the suggestion. We have a test server but I'm not sure if the Informix install is there or not. That would be the likely place to try though.

Thanks again.
Go to Top of Page
   

- Advertisement -