September 26, 2014 at 5:51 am
I am using SSIS 2008 and have created a package that utilizes an ODBC connection to extract data from our trouble ticketing system(ServiceNow). Because I need to extract data from several different tables I was hoping to develop some parallel Data Flow tasks, but have discovered that is problematic.
Scenario:
Two Data Flow tasks with SQL Commands selecting from different tables that returning records into different RecordSet variables, each using the same connection and no precedence constraints.
Test 1:Execute each Data Flow task independently - Both Data Flow tasks return records.
Test 2:Run the package so both Data Flow tasks will start in parallel - Both Data Flow tasks show as successful, however only one of them is actually returning records!
Test 3:Add a precedent constraint between the the two Data Flow tasks - Both Data Flow tasks return records.
Thinking that perhaps sharing the same connection might be causing the problem, I set up a second connection so that each Data Flow task had their own. When I attempted to run the package with that configuration I get and error like shown below for each table I am attempting to extract from.
I have tried turning on/off various combinations of DelayValidation and RetainConnection and have not struck gold yet.
Any help and/or suggestions are welcome!
Error: 0xC0047062 at Sys_Group 1, ADO NET Source [130]: System.Data.Odbc.OdbcException: ERROR [42S02] [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]Base table:sys_user_group not found.[10129]
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)
Error: 0xC004701A at Sys_Group 1, SSIS.Pipeline: component "ADO NET Source" (130) failed the pre-execute phase and returned error code 0x80131937.
Information: 0x4004300B at Sys_Group 1, SSIS.Pipeline: "component "Recordset Destination" (900)" wrote 0 rows.
Information: 0x40043009 at Sys_Group 1, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Sys_Group
October 14, 2014 at 8:26 am
I'm a few weeks late but I was searcing the web for an explanation of the error I'm getting and stumbled upon this. I'm also using SSIS 2008 to pull data from ServiceNow, we're moving away from linkedserver connection to the newer ServiceNow ODBC driver (32 bit) when I use the linkedserver openquery statement, I get records but if I attempt to use the ADO.net and pull from the servicenow ODBC driver I get the, "Base table:sys_user_group not found" all other MySQL tables I'm pulling work without an issue. Because you're running into a problem with the exact same table, I'm betting it's something with the ADO.net task in SSIS or either the ServiceNow driver itself. Please let me know if you resolved your issue.
ERROR [42S02] [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]Base table:sys_user_group not found.[10129] (ivoal24.dll)
[ADO NET Source [1731]] Error: System.Data.Odbc.OdbcException: ERROR [HY000] [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]Method failed: (https://...service-now.com/sys_user.do?SOAP&displayvalue=all&redirectSupported=true)HTTP/1.1 401 Unauthorized with code: 401[1001]
ERROR [HY000] [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]Method failed: (https://...service-now.com/sys_user.do?SOAP&displayvalue=all&redirectSupported=true)HTTP/1.1 401 Unauthorized with code: 401[1001]
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)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply