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.
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
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? |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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. |
|
|
|
|
|
|
|
|
SSIS ODBC Connection Manager Issue