DB2 and SSIS in SQL 2008

Question

Hi Folks,

I'm migrating an old SQL 2000 DTS package that used to execute a VB6 application, which would simply copy some data from our AS/400 (DB2) into the SQL 2000 database.

I figure since we now have SQL 2008 with SSIS, I can avoid the whole VB6 thing, and just create a real SSIS package to move the data.

My problem thus far is getting the DB2 connection (in connection manager in SSIS) to function correctly. It's a 64 bit server, so perhaps the 32 bit DB2 OLEDB stuff will not work?

The connection works when setting it up and testing in connection manager, but the minute I try to use a query in the (in the dataflow task) to grab the data, I get several errors:

[OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "172.xxx.xxx.xxx.B105xxx.ca" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[SSIS.Pipeline] Error: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

[SSIS.Pipeline] Error: One or more component failed validation.

[Connection manager "172.17.1.100.B105437D.ca95"] Error: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR.  The requested OLE DB provider IBMDA400.DataSource.1 is not registered -- perhaps no 64-bit provider is available.  Error code: 0x00000000.

An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".

Does anyone have any suggestions? Are the DB2 64 bit OLE DB drivers something that is available for free, or does this have to be purchased?

Thanks for any info.

Answer 1

Hi JojoBinkus,

First, I am glad you embarked on this endeavor.

Secondly, 64 Bit DB2 drivers are available from IBM, it depends on hardware and OS though, you can see here what is supported (you did not tell us by the way what you use to connect).

Now, based on the error description the driver/connection that the package tried to use is 64 Bit, but you have the 32 bit counterpart installed, thus I think the remedy is modifying the connection setting(s).

A question therefore now for you: where do you develop your package, on the server in BIDS?

---

Arthur My Blog

Answer 2

Yes, I am developing the package on the SQL 2008 server in BIDS. I have the 32 bit drivers for DB2 installed on this server, which we used to use on the SQL 2000 server.

I'm not a "driver" expert by any means (obviously), but in connection manager, I have selected the following:

For "Provider":
Native OLE DB\IBM DB2 UDB for iSeries OLE DB Provider

For "OLE DB Provider":
IBM DB2 UDB for iSeries OLE DB Provider 

Please let me know if I'm not providing you the correct information...I appreciate your reply!

Thanks!

Answer 3

I suggest you develop you package locally.

What the current setting of Run64BitRuntime is?

---

Arthur My Blog

Answer 4

I guess I could develop the package locally - but for now, BIDS is already installed on the server, and not on my local machine.

I am not familiar with Run64BitRuntime?

Where do I check these settings?

Thanks!

Answer 5

Please visit this link -

http://blogs.msdn.com/b/amitlale/archive/2007/12/11/ssis-64-bit-setting.aspx

(I gave you the link because it has more info than I would type)

In short, I suggest flipping the setting regardless of the original value in it.

---

Arthur My Blog

Answer 6

Thanks - that took care of the warning - but it still fails with other errors -

 

I think what this comes down to is I have the 32bit drivers installed for the iSeries DB2, and I really need the 64 bit drivers...

Problem is, I can't seem to find them anywhere to download - I was told I could get them for free from IBM, but they don't seem to be easy to find.

 

If anyone knows where to download them, that would be awesome!

Answer 7

Glad to hear that helped.

Go to http://www-01.ibm.com/software/data/db2/db2connect/ -> Download and specify what you need (for server you need the server edition of "connect" product).

---

Arthur My Blog

Answer 8

Thanks for the link - just found out that you can only download "fixes" - the actual "DB2 Connect" has to be purchased...

 

So much for the folks who told me this was "free"...

 

Thanks for the info!

Answer 9

Correct, it is a paid product. Normally the purchase is done through a third-party vendor.

Since you already have one you I guess need to ask whoever obtained the 32 bit version to get the 64 counterpart.

---

Arthur My Blog

Answer 10

Thanks for the link - just found out that you can only download "fixes" - the actual "DB2 Connect" has to be purchased...

 

So much for the folks who told me this was "free"...

 

Thanks for the info!

Have you checked IBM Data Server Runtime Client? I believe it is free.

---

SSIS Tasks Components Scripts Services | http://www.cozyroc.com/