BJ Custard


Anyone writing data to DB2 on an AS400 with SSIS

I cannot get the OLEDB destination configured correctly. I can set the destination up with a SELECT sql query, and preview the resultset.




Re: Move data to DB2 on AS400 with SSIS

David Lundell


BJ,

The ODBC connection is easier to setup.

Nonetheless, what problem are you having Please be specific.







Re: Move data to DB2 on AS400 with SSIS

Phil Brammer

What is the error

Also, you can't use an out-of-the-box solution against an ODBC destination. You are correct in using the OLEDB for DB2 driver. I use it for my source pulls, but don't use it for destinations so I'm not much help there without knowing your error.






Re: Move data to DB2 on AS400 with SSIS

Ryan.Kelley

Yes, we have.

I have found the IBM OLEDB provider easier to use than the MS driver for DB2. Are you able to establish a connection I could not tell from your post.





Re: Move data to DB2 on AS400 with SSIS

BJ Custard

Attempt #1

When I try to set up a data source / conn mgr with the MICROSOFT OLE DB PROVIDER FOR DB2, I get an error when I test the connection:

"Test connection failed because of an error in initializing provider. The parameter is incorrect."

I have the login and password correct, and I get the same error regardless of whether I have the server name or the IP listed, and whether I provide an Initial Catalog or do not. I always get the same error.




Re: Move data to DB2 on AS400 with SSIS

Ryan.Kelley

I have been using "attempt #2".

Under IBM OLEDB provider for IBM , click on "data links".

From here, You have two options: You can register the data source on the machine. and use "existing data source". (We were able to use this method as we have control ove rthe box that the package is running on.)

or use the direct connection: where you need to enter the server dns name and the database that you need to connect to. they are in seperate input boxes. (may need help from aix admin.)

we were just joking recently about how we could quit our jobs and make a good living as consultants going around and setting up connections to DB2 data sources.

soo.. it is a little tricky. prepared to be locked out of the DB at least once. :)





Re: Move data to DB2 on AS400 with SSIS

BJ Custard

Using #2, I can get data pulled out of the AS400 table to a flat file. Yay!

However, my task is to write data into the AS400 table. When I set it up as an OLE DB Destination, I get the following error...

Error at Data Flow Task [OLE DB Destination [16]]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "IBMDA400 File Rowset" Hresult: 0x80004005 Description: "CPF4328: Member IMSRSRP not journaled to journal *N.
".

Error at Data Flow Task [OLE DB Destination [16]]: Opening a rowset for "INTDTATST.IMSRSRP" failed. Check that the object exists in the database.

Any ideas I am pretty sure I have permission to the table, as I'm the one who put the data in it to begin with (via iSeries)... TIA





Re: Move data to DB2 on AS400 with SSIS

Ryan.Kelley

BJ,

That is an error specific to the as400 enviornment. you are connecting to the db and writing to the table.

look into this error code CPF4328

Target table is not being journaled

Cause

Return code 7356 is displayed when the target table is not being journaled. To verify that this is the problem, check the AS/400 agent job log.





Re: Move data to DB2 on AS400 with SSIS

BJ Custard

Thanks Ryan. I got the data into the AS400.

I'd post the exact settings if anyone wants them, but right now I'm off to start my consulting business setting up SSIS-AS400 connections.

Thanks again!

--BJ





Re: Move data to DB2 on AS400 with SSIS

Phil Brammer

BJ Custard wrote:

Thanks Ryan. I got the data into the AS400.

I'd post the exact settings if anyone wants them, but right now I'm off to start my consulting business setting up SSIS-AS400 connections.

Thanks again!

--BJ



If your settings pertain to SSIS configuration, then yes, please share so that we have it documented.





Re: Move data to DB2 on AS400 with SSIS

BJ Custard

The configuration steps are as follows...

  1. Create a Data Source for the AS/400, using provider NATIVE OLE DB\IBM DB2 UDB FOR ISERIES IBMDA400 OLE DB PROVIDER, WITH THE FOLLOWING CHANGES
    • CATALOG LIBRARY LIST is the AS/400 library
    • USER ID should be populated
    • PASSWORD should be populated
    • PERSIST SECURITY INFO should be TRUE
    • DATA SOURCE should be your machine DNS name (tho IP might work)
    • INITIAL CATALOG should be the actual machine name (mine is populated when I click on the dropdown)
  2. Create a Connection Manager for the above data source
  3. Create an OLE DB source/destination...
    • OLE DB Connection Manager from above
    • Data access mode = Table or View (or SQL Command for a OLE DB Source)
    • Name of Table/View should be <Catalog Library List>.<Table>. If you config is correct, this dropdown will contain a list of them to select from...
    • On the 'Advance Editor' screen, under Component Properties, set
      • VALIDATE EXTERNAL METADATA = FALSE
      • ALWAYS USE DEFAULT CODE PAGE = TRUE
  4. We had to turn on JOURNALLING on the DB2 tables to write to them. (This may or may not be a requirement...)

Hope this helps...





Re: Move data to DB2 on AS400 with SSIS

AaronBrock

I am using IBMDA400 driver in SQL integration services. I am using the oleDB destination to the AS400. I do not want to turn on Journaling on the tables I am writing to. I need to figure out a way to turn off Commitment control with using the IBMDA400 driver properties. Can anybody help with me with this



Re: Move data to DB2 on AS400 with SSIS

AaronBrock

**BUMP** I still need help with turning off Commitment control on IBMDA400 connection.



Re: Move data to DB2 on AS400 with SSIS

J5

The information from BJ Custard helped a ton, but there were a couple other steps I had to do in order to get my data from SQL to AS400 DB2. First of all, I do not have a Native OLE DB\IBM DB2 UDB FOR ISERIES IBMDA400 OLE DB PROVIDER, but I used Native OLE\IBM DB2 UDB for iSeries OLE DB Provider. Secondly, I ran into data type errors (data mapping) and found out SSIS does not like datetime fields (type z) on the iSeries nor does it like mapping null SQL fields to an AS400 table unless the AS400 table specifies allow nulls. That's it...... I hope this helps!!!



Re: Move data to DB2 on AS400 with SSIS

Alan Frye

Did you ever get this resolved. I am having a similar issue with writing sql datetime fields to the db timestamp field on the ISeries. The timestamp field kas the following format 'yyyy-mm-dd-hh-mi-ss'. Any ideas