OLE DB & SQL

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" columns need to be 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 dynamically from a local variable. This is possible with the Oledb Src.

What should I do. Any idea ?