Does anyone know why SQL doesn't work properly in my OLE DB component? Underlying DB: DB2 Provider: IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider If I insert some SQL I get get a preview of the data but it errors when I run it. [OLE DB Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E00. [DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. [DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038. [DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039. I can set the data access mode to 'table or view/OpenRowset' and that works fine. :( Please help....

I also faced the same problem, Then instead of Oledb Src adapter I configured Data Reader Source. For the connection manager I created a ADO.Net Connection manager Create a DSN, used a ADO.Net Provider for ODBC this will create a ADO.Net Connection Manager. Now you can Configure Data Reader Source...Workaround ...:) Thanks Dharmbir

Awesome! That works.Many thanks Dharmbir.

I also used your solution! but you dont have to forget to change the value of the Connect Time out wich standard value ist 0.

HI, I used this option ( ADO.NET - ODBC combination ) but i guess ODBC does not support substring. I need to have substring of a particular column as criteria to extract data from AS400(source) and insert that into the DB2/SQL server(destination) and that's where i'm stuck.my query looks like this:"SELECT * FROM itcga.xxxxx where Substr(itgdta.xxxxx.GENLCDE,9,2) = '61'"Contrast to this if i use the OLEDB source, i get the following error message:"Error: 0xC0047021 at XXXXXX, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039."Please Help on getting data form as400 with the where substring condition.Thanks in advance.Amit S

Another possibility is to setup the DB2 database as a linked database in SQL Server 2005, create views on that linked database within a SQL Server 2005 database, and finally create an OLE DB source in Integration Services using the SQL Server 2005 database and linked views.

Amitshah, ODBC is just a set of API's that connect to the underlying Database. SISS does not do explicit conversions, so most often "varchar" columnsneed tobe converted. In the case above, it appears that two explict conversions are being done on the data before it lands in the source DB. once to ADO.NET and again to ODBC? I cannot tell what your destination DB is... however, SQL Server DB does not recognize the substr() function. Try Substring() The Microsoft Whitepaper on the front of the Forum does a great job of explaining these concepts.

This works for me too. But it doesn't solve my problem. If you use Data Reader Source it is not possible to set the select-statement dynamicallyfrom a local variable. This is possible with the Oledb Src. What should I do. Any idea ???

hi, in AS400 you can use Substr and Substring but the field within must be alphanumeric. If the field from you want extract is numeric you can use digits to convert it. If GENLCDE is numeric you must insert the digits function like this: Substr(digits(itgdta.xxxxx.GENLCDE),9,2)

IBM has a known issue with their OLEDB provider and SSIS which causes "SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E00."I'm no IBM expert, but their doc says they have a fixhttp://www-912.ibm.com/n_dir/nas4apar.NSF/c79815e083182fec862564c00079d117/a802b6eb29d32cac8625726c0041efc9?OpenDocumenthttp://www-01.ibm.com/support/docview.wss?uid=nas2a802b6eb29d32cac8625726c0041efc9http://www-933.ibm.com/eserver/support/fixes/fixcentral/fixdetails?multi=y&fixid=SI28055Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

Hi , I upgraded my package from 2005 to 2008 and instead of query i am directly calling an Stored procedure in the Sql Statement, The Stored Procedure is getting executed for me with out any problems if i run it from backend, but if i run the SSIS package , it is giving the following error: Error: 0xC002F210 at Get FileIds to be purged, Execute SQL Task: Executing the query "CALL dbo.GetWarehousesToBeProcessed_sp (?, 1)" failed with the following error: "Exception from HRESULT: 0x80040E00". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. can anyone help me why i am facing this problem? Thanks in advance Shilpa

Hi, in the substr function take care with the space before numbers. Must be a space between a comma and number. Substr(digits(itgdta.xxxxx.GENLCDE), 9, 2) I hope this help you.