SSIS ERROR : System.Data.Odbc.OdbcException: ERROR [08S01] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Socket closed

Question

I have a serious problem with unstable connexions for few source tables. In the connexion manager the login succeeds, when I open the ADO.NET Source editor I can preview the first 200 rows, when I use the build query to display all rows, it works (very slowly).

When I execute the package with debugging button, In error case I can see that:

1/ The ADO.NET Source in red and the Destination in green (the number of rows doesn't entirely loaded in the destination Table)

2/ The ADO.NET Source in red and the destination uncolored (0 rows loaded)

The error message:

    Source Facture [1]] Erreur : « System.Data.Odbc.OdbcException: ERROR [08S01] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Socket closed.
   à System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   à System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
   à System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
   à System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   à System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   à System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   à System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   à System.Data.Odbc.OdbcConnection.Open()
   à Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
   à Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)
   à Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
   à Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction) »

I think that is related to the timeout, if I can change the timeout to give more time for loading data from the source table.

Answer 1

You mention SSIS. Are you running these queries as pass-through queries? You probably should be.

Two other things that often mess up ODBC clients accessing OpenEdge databases are:

1) Failing to have ever run UPDATE STATISTICS. Applications that use the OpenEdge database are usually 4GL applications. The 4GL query optimizer is a compile-time rule based optimizer. The SQL-92 engine (which ODBC uses) is a cost based optimizer. Often times SQL usage is an afterthought and the administrator (if there is one) is unaware of the need to run statistics in order to provide the optimizer with data in order to efficently run queries. Without any statistics performance is usually pretty bad because even trivial queries become table scans.

http://knowledgebase.progress.com/articles/Article/20992

2) Your posted error messages do not appear to be related to this but you may also need to run dbtool to adjust field widths (OpenEdge fields are all variable width and can be over-stuffed -- which gives SQL clients fits.)

http://knowledgebase.progress.com/articles/Article/P24496

Answer 2

I had similar problems with OpenEdge ODBC-driver when executing SSIS tasks. The error msg was something like, The AcquireConnection method call to connection manager failed. Lots of googling but no help. Finally it turned out that that OE's ODBC driver cannot handle parallel execution of data flows. As soon as I divided them in separate control flow tasks, things started to work. Hope this helps someone. :)