October 4, 2006 at 12:07 pm
Hi,
I am trying to create an SSIS package that exports data from an AS400/DB2 database to a SQL server 2000 database. The Provider is IBMDA400 OLE DB. I ran the SSIS package and the following error occurred:
[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 found out through a Microsoft forum that you need to create a DSN and use the DSN is an ADO.Net connection and then configure a Datareader source.
My question is how can I use that Datareader source in a Lookup transformation. The only option I am given is OLE DB source. If a use the AS400/DB2 source, the package will fail with error code 0x80040E00.
Any help is appreciated!
October 9, 2006 at 8:00 am
This was removed by the editor as SPAM
October 11, 2007 at 6:13 am
I have the same problem using SQL2005 and DB2. IBM said to put PTF R540 SI26879 7226 on the AS400. I did and it still will not work. Any body with the answer????
December 31, 2007 at 3:03 pm
Try downloading the Feature Pack from Microsoft and using their drivers to connect to DB2.
This has worked for me since I could not get the IBM drivers to work correctly even after applying the PTFs.
January 2, 2008 at 7:20 am
A bit of a warning.
The DB2 Driver in the Feature pack will only work with
SQL Server 2005 Developer Edition
OR
SQL Server 2005 Enterprise Edition.
If using SQL Server 2005 Standard Edition. We have successfully created a 2-step connection process to our AS/400. We create an ODBC connection using the "IBM iSeries Access for Windows" driver. Then we create a Linked Server using the provider: "Microsoft OLE DB Provider for ODBC Drivers". We can then connect to the iSeries data using OPENQUERY.
Hope This Helps.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 2, 2008 at 12:28 pm
Damon, that's a pretty slick work around. Does it convert everything to unicode though?
Wayne as for the PTF, we did some reseach and once that PTF is installed, version SI28055 is required for your Client Access. I brought this update to our test server and the OLE DB connections from IBM now work correctly. I did my tests using the following provider.
IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider
I tested this as:
1) A source table/view
2) Sql Command
3) Sql Command from a parameter
4) Lookup
5) OLE DB Target
The all worked correctly and it seems to be faster than the Microsoft version of the provider.
Hope this helps.
Chris Sinkwitz
January 2, 2008 at 1:03 pm
Several days of trial and error got us the proper driver combination.
I ran this by the other DBA as well. We have not have any issues with UNICODE with the OLEDB drivers, I **THINK** that the drivers handle the UNICODE issues "on the fly". We have had a production system in place using the 2-step SQL Server Standard Edition 2005 solution since 15NOV2007 without any data issues.
DB2 is ISO based and SQL Server 2005 is UNICODE.
Hope this helps.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
February 13, 2008 at 6:40 am
Hi
I've tried the recommendation for Standard Edition but get the following error
Cannot initialize the data source object of OLE DB provider "IBMDA400" for linked server "development".
The PTF has already been applied as well.
Anyone know where I'm going wrong?
February 13, 2008 at 7:48 am
Sameeg,
You will need you use "MSDASQL" provider (This is the provider for using ODBC connections). I was actually able to use the ODBC System DSN and ODBC Linked Server combination last night ... AND I took notes!
(Excuse my personal note taking style. You get a little insight on how I write, for better or worse!)
-- Step 0) Must have IBM iSeries Access for Windows client installed.
--
-- Step 1) Create the System DSN
--
-- Step 2) Create the ODBC Linked Server
--
EXEC sp_addlinkedserver
@server='DB2_ODBC', -- Name of the Linked Server, when it is created.
@srvproduct='Microsoft OLE DB Provider for ODBC', -- OLE DB Provider.
@catalog='', -- Catalog Is Optional For ODBC Connections.
@provider='MSDASQL', -- Provider_name should be MSDASQL.
@datasrc ='SYSTEM_DSN_NAME', -- System DSN of ODBC Data Source.
@provstr= -- ODBC Connection String.
'
DRIVER={DB2};SERVER=YOUR_INITIAL_CATALOG;UID=YOUR_USER_ID;PWD=YOUR_PASSWORD;
'
-- Note:
-- SERVER is the Initial Catalog for connecting to DB2.
-- Check the connection via OPENQUERY
SELECT * FROM OPENQUERY
(DB2_ODBC,
'
--YOUR DB2 SQL Syntax Statement HERE --
')
GO
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
February 13, 2008 at 7:54 am
Chris,
I believe that the unicode portion comes through properly. I have a SQL Server 2000 (SP 4) ETL process that uses DTS from our DB2 system, and don't have any issues regarding unicode/ansi or collation related issues with the data as I work with it in SQL Server 2000.
I am just beginning to "reach into" DB2 with our SQL Server 2005 systems and/or using SSIS with DB2 as well.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
March 7, 2008 at 11:41 am
Great info. I was able to create the linked server as outlined by Damon and was successful on creating queries. My question is how can I implement that into an Integration Services project? :unsure:
I've added an 'Execute SQL Task' to my control flow tab, which uses the OPENQUERY and linked server. How can I take the result set and import into a local sql db?
thanks!
March 7, 2008 at 12:20 pm
rbateman,
Glad to hear that my notes are a benefit to someone else.
For use in an SSIS package, you will need to configure your SQL Server with the Linked Server as a Connection. From there you should be able to use OPENQUERY as if you were querying a SQL Server database, in your SQL Execute Task. (I am still a little green with SSIS, but I like what I have been able to accomplish so far.)
I am working on a high priority/high profile issue today. I can go into more detail if you are stuck. It helps if you can give as much detail on your end as well.
Cheers,
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply