June 9, 2009 at 1:07 pm
I am trying to help two different developers who are hitting the same generic "An OLE DB error has occurred. Error code: 0x80040E53." error when they execute their SSIS packages. One is connecting to a DB2 database, the other to a UDB database. Both use the IBM DB2 OLE DB provider, and both find that they can run tests against these connection managers via executing SQL tasks, but as soon as they try to do "real work" in data flow tasks with the same connection managers, their packages fail with the generic error msg noted above. Within the error log, it is hard to determine just what the failure really is (security? overflow? connection string problem?), because in both cases, the preceding event is the pre-execute event for the data flow task.
Is there a better way to debug this problem, where we might be able to get a more descriptive error message? Does anyone know of any best practices when it comes to working with either DB2 or UDB and SSIS? It seems that SSIS works great with SQL Server or flat file sources & destinations, but we are still seeking the holy grail solution when working with DB2 or UDB.
Thanks!
June 10, 2009 at 3:46 pm
UPDATE: I found the following link: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/SSIS-Nugget_3A00_-Verify-a-data-source-before-using-it.aspx which was very helpful. However, I'm adding logging to capture the exception object info, but am not able to get the same exception info logged as is available in the output window.
In the output window, I see:
Error: 0xC0202009 at PackageName, Connection manager " SQL Server Source Database": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user 'DOMAIN\myUserID'.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot open database "databasename" requested by the login. The login failed.".
But, in the log where I am logging the exception Message, To String, etc, I am not able to get that full info. I'm using a stringbuilder object to append the various exception properties together as seen below. But, I can't get either of those "OLE DB record is available..." messages to my log. Are they stored in the Exception collection available to the Try...Catch block, or are they some other error?
sbExInfo.Append(vbCrLf)
sbExInfo.Append("Exception String: ")
sbExInfo.Append(ex.ToString())
sbExInfo.Append(vbCrLf)
sbExInfo.Append("Exception Base Exception: ")
sbExInfo.Append(ex.GetBaseException.ToString)
sbExInfo.Append(vbCrLf)
sbExInfo.Append("Exception Message: ")
sbExInfo.Append(ex.Message)
sbExInfo.Append(vbCrLf)
sbExInfo.Append("Inner Exception: ")
sbExInfo.Append(ex.InnerException)
sbExInfo.Append(vbCrLf)
sbExInfo.Append("Source: ")
sbExInfo.Append(ex.Source)
sbExInfo.Append(vbCrLf)
sbExInfo.Append("Help Link: ")
sbExInfo.Append(ex.HelpLink)
June 12, 2009 at 12:55 pm
We've now tried running this package on two different servers, with two different accounts, but the results are the same. The problem seems to be that we can acquire a connection successfully, but when we try to push to the destination, we fail. The accounts we've used have all the access they need to insert into the table, so that's not it. Any ideas out there? This thread is getting viewed a lot, anyway.
Just in case this jogs anything for anyone, here's an excerpt from a recent run:
Error: 2009-06-12 13:26:31.21
Code: 0xC0202009
Source: Package Data Flow Task Destination Configuration Manager [398]
Description: An OLE DB error has occurred. Error code: 0x80040E53.
End Error
Error: 2009-06-12 13:26:31.23
Code: 0xC0047022
Source: Package Data Flow Task Destination Configuration Manager DTS.Pipeline
Description: The ProcessInput method on component "Destination Configuration Manager " (398) failed with error code 0xC0202009. The identified component
returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
End Error
June 19, 2009 at 11:30 am
As it turns out, all was not lost. I found this forum thread, and found a solution (or a good enough one) within it: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/96a2c8d9-6a9e-4f31-9449-0712f9bc2cbc
The solution that was the easiest to work with was the post by Jan:
"[font="Tahoma"]there is a solution to use ODBC destinations: simply create a new OLEDB connection in Conection Manager. As provider select "MSDataShape" - THIS IS THE OLEDB-ODBC-DRIVER (MSDASQL)! As Data Source you can type in your ODBC-DSN.
Then put a OLE DB destination object to the data flow pane and select the previously created connection in "OLE DB Connection Manager". That's it!
The transfer to MySQL seems to be very slow. I had to decrease the value for DefaultBufferMaxRows (property of the data flow pane) from 10000 to 200 to get a useful progress view (transferes only 200 rows per second, BUT IT WORKS )! [/font] "
This hasn't been implemented this in Prod yet, but Demo testing has been very promising. The developer did still have to do the usual transformations to get data types properly converted, but no more maddening generic OLE DB errors.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply