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.

OLEDB for DB2 how to ...

Hello,
I've installed the Host Integration server client SP1 on my development
machine running SQL server. I'm tryiyng to create a udl file to connect to
the DB server but I allways get an error about wrong parameters ... can
anyone help me out?
The idea is to be able to run a dts task against the DB2 thx!OK the connection test worksm
now I struggling with the meaning and significance of collection and schema
options ...
Tips are welcome
"John Koswalski" <john.koswalski@.shell.nl> wrote in message
news:umv9fhe2DHA.2856@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hello,
> I've installed the Host Integration server client SP1 on my development
> machine running SQL server. I'm tryiyng to create a udl file to connect

---

to

quote:

---

> the DB server but I allways get an error about wrong parameters ... can
> anyone help me out?
> The idea is to be able to run a dts task against the DB2 thx!
>

---

OLE DB2 Provider

Hi

Running SQL 2005 standard edition, using OLE DB2 provider from Host integration server. When connected we can acces data with select statements, but cannot browse tables from import wizard in SSIS.

Is there a hint or solution?

rgds

It's probably the provider. The Enterprise Edition of Sql Server comes with a DB2 provider. You could also use an ODBC connection instead and see if that works. Finally, IBM might have a provider as well?|||Do you get an error message? I'm using the MS OLE DB2 provider quite regularly and have no issues... Setup is key, though, to getting it to work correctly.

OLE DB2 driver for Reporting Services

Question: When will the OLEDB driver be available for Reporting Services? In the Marketing Material I noted that Reporting Services can indeed connect to AS/400 through OLEDB. I have tried with all supplied OLEDB drivers and get only errors. The only drivers which work are ODBC and they are dog-slow. I have reviewed most posts on OLEDB drivers for AS/400 or DB2 and most everything points to SSIS. I want to be able to connect to the AS/400 through Reporting Services (SSRS) and report off it via an OLEDB driver instead of ODBC as I am doing it right now. Are there ANY implementable alternatives out there anybody?According to the SQL Server Feature Pack, the Microsoft OLEDB for DB2 driver can be used with Reporting Services.

http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

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 for DB2 Built on Dev run on Standard

I have built a simple package using the Microsoft OLE DB provider for DB2 with SSIS Developer. If I schedule this on a Standard SQL2005 machine the package will not run. The error is blank, just says it had an error. Can I not run this package because the provider is not installed on this machine? If so is there a work around for this?you probably have to run your package on the box that has DB2OLEDB physically installed.|||

I believe that the ole db for DB2 has to be used on enterprise edition (if i read the licesne right from the ssis extension site). I am not sure if the ms one works very well. The IBM one sucked very badly when dealing with decimals. Even they recommended not using it (IBM) as part of the Client access express in their redbooks. If you are not going to fork out the extra few thousand bucks for enterprise, try the ODBC connect from DataDirect. It is a bazillion times faster than the CLAE ODBC, and i could actually get it to work (on db2 400).

Dev edition has all rights/ features as enterprise.

OLE DB Destination problem - with keywords

Destination table has a column name - Partition that is a key word in the DB2 database. But database would execute the query fine if there is "" around the column name - "Partition". When an OLE DB Destination component is used, it throws an error saying column name is a reserved word and that it cannot be used. When put "" around the column name, it fails in the pre-execute phase thinking that "Partition" is the column name instead of Partition. Any ideas to fix this problem would be greatly appreciated?

What OLE driver are you using for DB2?|||

Phil,

I am using 'IBM OLE DB Provider for DB2'. Any suggestions on what I should do to fix the problem?

|||First, do square brakets work? [Partition]

Second, is your data flow column called Partition, or something else and you're mapping it to a column named Partition? What I'm getting at here, is you can alias the column in the data flow to be something other than "Partition." That might help.

Also, you could try using the Microsoft OLE for DB2 driver.

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 ?