oledb to ibm db2 - no tables?

Hi.
I am upgrading a datawarehouse from sql2000 to sql2005.
Unfortunately, there is something wrong when I try to get the list of
available tables/views to import from ibm as400.
It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
there is no such driver available on sql2005. The only available driver is
the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the list
of all the available tables / libraries on the remote system. The message
is: "The source database you have selected contains no visible tables or
views." On sql2000 I was able to fetch the list of all the available tables
in all the libraries, put a tick against some of them and just import them
to the sql server.
I noticed that I can use the .Net driver for ODBC which allows me to import
data from any odbc source (defined in control panel -> administrative tools
-> odbc...), but this approach requires typing the whole sql statement for
each source table. This could be OK for a single table, but there are more
than 50 tables I need to import and writing separate sql statement for each
of them is something I would like to avoid.
Is there anybody with good experience regarding this issue? Please help...
--
PLI'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
When selecting from libraries files I simply do " SELECT * FROM
AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
Here is my linked server script, hope it will help you:
(our AS/400 system name is.. AS400 :-) ).
/****** Object: LinkedServer [AS400OLE] Script Date: 04/16/2007 16:58:00
******/
EXEC master.dbo.sp_addlinkedserver @.server = N'AS400OLE',
@.srvproduct=N'AS400', @.provider=N'IBMDA400', @.datasrc=N'AS400',
@.catalog=N'AS400'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'AS400OLE',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'AS400USERID',@.rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'dist',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'pub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'sub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'lazy schema
validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'use remote
collation', @.optvalue=N'true'
"Piotr Lipski" <piotr.lipski@.nie.p0czta.spamuj.bo.onet.w.ryj.pl> wrote in
message news:1rjimvs4uyts7$.3ctxiz7tm0uv.dlg@.40tude.net...
> Hi.
> I am upgrading a datawarehouse from sql2000 to sql2005.
> Unfortunately, there is something wrong when I try to get the list of
> available tables/views to import from ibm as400.
> It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
> there is no such driver available on sql2005. The only available driver is
> the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the
> list
> of all the available tables / libraries on the remote system. The message
> is: "The source database you have selected contains no visible tables or
> views." On sql2000 I was able to fetch the list of all the available
> tables
> in all the libraries, put a tick against some of them and just import them
> to the sql server.
> I noticed that I can use the .Net driver for ODBC which allows me to
> import
> data from any odbc source (defined in control panel -> administrative
> tools
> -> odbc...), but this approach requires typing the whole sql statement for
> each source table. This could be OK for a single table, but there are more
> than 50 tables I need to import and writing separate sql statement for
> each
> of them is something I would like to avoid.
> Is there anybody with good experience regarding this issue? Please help...
> --
> PL|||On Mon, 16 Apr 2007 17:00:42 -0400, Rafael Lenartowicz wrote:
Thanks for hint. In the meantime I've re-installed the whole as400 software
suite, choosing "full" option this time - and it definitely works better.
At least, I can get the list of all the tables on remote system.
Unfortunately, I still cannot get the full list of all the columns in a
particular table - I have to run "select * from [as400table]" to do it.
Anyway, I'm happy enough. I can import data (and schedule the import
packages). Next step will be learning how to fire ssis packages on demand.
Cheers,
PL
> I'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
> When selecting from libraries files I simply do " SELECT * FROM
> AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
> Here is my linked server script, hope it will help you:
[...]

oledb to ibm db2 - no tables?

Hi.
I am upgrading a datawarehouse from sql2000 to sql2005.
Unfortunately, there is something wrong when I try to get the list of
available tables/views to import from ibm as400.
It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
there is no such driver available on sql2005. The only available driver is
the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the list
of all the available tables / libraries on the remote system. The message
is: "The source database you have selected contains no visible tables or
views." On sql2000 I was able to fetch the list of all the available tables
in all the libraries, put a tick against some of them and just import them
to the sql server.
I noticed that I can use the .Net driver for ODBC which allows me to import
data from any odbc source (defined in control panel -> administrative tools
-> odbc...), but this approach requires typing the whole sql statement for
each source table. This could be OK for a single table, but there are more
than 50 tables I need to import and writing separate sql statement for each
of them is something I would like to avoid.
Is there anybody with good experience regarding this issue? Please help...
PLI'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
When selecting from libraries files I simply do " SELECT * FROM
AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
Here is my linked server script, hope it will help you:
(our AS/400 system name is.. AS400 :-) ).
/****** Object: LinkedServer [AS400OLE] Script Date: 04/16/2007 16:58:00
******/
EXEC master.dbo.sp_addlinkedserver @.server = N'AS400OLE',
@.srvproduct=N'AS400', @.provider=N'IBMDA400', @.datasrc=N'AS400',
@.catalog=N'AS400'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'AS400OLE',@.useself=N'False
',@.locallogin=NULL,@.rmtuser=N'AS400U
SERID',@.rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'dist',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'pub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'sub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'lazy schema
validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'use remote
collation', @.optvalue=N'true'
"Piotr Lipski" <piotr.lipski@.nie.p0czta.spamuj.bo.onet.w.ryj.pl> wrote in
message news:1rjimvs4uyts7$.3ctxiz7tm0uv.dlg@.40tude.net...
> Hi.
> I am upgrading a datawarehouse from sql2000 to sql2005.
> Unfortunately, there is something wrong when I try to get the list of
> available tables/views to import from ibm as400.
> It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
> there is no such driver available on sql2005. The only available driver is
> the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the
> list
> of all the available tables / libraries on the remote system. The message
> is: "The source database you have selected contains no visible tables or
> views." On sql2000 I was able to fetch the list of all the available
> tables
> in all the libraries, put a tick against some of them and just import them
> to the sql server.
> I noticed that I can use the .Net driver for ODBC which allows me to
> import
> data from any odbc source (defined in control panel -> administrative
> tools
> -> odbc...), but this approach requires typing the whole sql statement for
> each source table. This could be OK for a single table, but there are more
> than 50 tables I need to import and writing separate sql statement for
> each
> of them is something I would like to avoid.
> Is there anybody with good experience regarding this issue? Please help...
> --
> PL|||On Mon, 16 Apr 2007 17:00:42 -0400, Rafael Lenartowicz wrote:
Thanks for hint. In the meantime I've re-installed the whole as400 software
suite, choosing "full" option this time - and it definitely works better.
At least, I can get the list of all the tables on remote system.
Unfortunately, I still cannot get the full list of all the columns in a
particular table - I have to run "select * from [as400table]" to do it.
Anyway, I'm happy enough. I can import data (and schedule the import
packages). Next step will be learning how to fire ssis packages on demand.
Cheers,
PL

> I'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
> When selecting from libraries files I simply do " SELECT * FROM
> AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too
!)
> Here is my linked server script, hope it will help you:
[...]sql

Oledb Source adapter exception Error code: 0x80040E21

Hi

I am using Oledb Provider for Db2 from IBM. (There is another one from microsoft)

Through this provider I am constructing a oledb connection manager.

This connection manager I am Using in Oledb Source adapter.

Now when I set Data Access mode as Table or view I Am able to preview the data.

But when I use Data Access Mode as SQL Command try to preview I get this Error

TITLE: Microsoft Visual Studio

Error at s_NEWMAPPING10 [EMP [1]]: An OLE DB error has occurred. Error code: 0x80040E21.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

I am not able to figure out how to resolve this.

I am receiving the exact same error. Can't run custom queries against the database and it is frustrating. Someone from Microsoft needs to respond to this immediately...it's not acceptable.sql

Oledb Source adapter exception Error code: 0x80040E21

Hi

I am using Oledb Provider for Db2 from IBM. (There is another one from microsoft)

Through this provider I am constructing a oledb connection manager.

This connection manager I am Using in Oledb Source adapter.

Now when I set Data Access mode as Table or view I Am able to preview the data.

But when I use Data Access Mode as SQL Command try to preview I get this Error

TITLE: Microsoft Visual Studio

Error at s_NEWMAPPING10 [EMP [1]]: An OLE DB error has occurred. Error code: 0x80040E21.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

I am not able to figure out how to resolve this.

I am receiving the exact same error. Can't run custom queries against the database and it is frustrating. Someone from Microsoft needs to respond to this immediately...it's not acceptable.

OLE DB Provider - Saving SQL UserName and Password error

I have created an SSIS package, which uses an IBM OLE DB Provider for DB2 to access some data. In this case it is not possible to use

Windows NT Integrated Security, so therefore I stored the username and password in the connection.

It works fine for the current user, but if I log on to Windows with another user and try to change the OLE DB connection I recive the following error:

Error 1 Error loading BaliAdslUge.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "The key is not valid" (in danish "N?glen er ikke gyldig for anvendelse i den angivne tilstand.").

You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. c:\_data\projects\baliadsl\integrationservices\BaliAdslUge.dtsx 1 1

Error 2 Validation error. BaliAdsl: OLE DB Source [3075]: The AcquireConnection method call to the connection manager "Pmarco.f00337" failed with error code 0xC0202009. BaliAdslUge.dtsx 0 0

Best regards,

Ejnar Jakobsgaard

TDC

Ejnar,

By default we encrypt sensitive information with the user key. You need to change this to encrypt with password if you want multiple users to be able to decrypt it.

Thanks,|||Hi Matt,

Could you provide some further information on this issue?
How do I encrypted with password, so that several users can use this connection?

What is your advice, if I cannot use Thrusted connection and the user developing the package and running them - Is not the same user?

Best regards,

Ejnar|||Hi Matt,

Thanks :-)

In found some info. in online help at

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/d4b073c4-4238-41fc-a258-4e114216e185.htm

Regards,

Jakobsgaard

OLE DB Error in SSIS

Hi All,

We are using IBM iseries OLE DB provider and once we make a source connection using the same we get an error saying that the "Default Code Page" property is not defined and a default code page will be used.

Any ideas?

Thanks

Manish

Set AlwaysUseDefaultCodePage=TRUE on the source component.

-Jamie

|||

Thanks Jamie.

Tried it out but it also does not seem to work, though there are no validation warnings, the job runs and give a Pipeline PrimeOutput Error()

|||

That's probably a different error. i.e. Related to something else.

-Jamie

|||

I still feel its an OLE DB error because it starts with an error saying OLE db error with a hexadecimal value.

Any ideas?

|||

Yes, that was my point. i.e. The default code page is not the problem.

I personally have no experience of extracting from these systems.

You may want to try the data access forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

-Jamie

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 ?

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 ?

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 ?

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 ?

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 ?