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.

BJ, The ODBC connection is easier to setup. Nonetheless, what problem are you having? Please be specific.

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.

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.

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. Attempt #2 When I set up a data source with the NATIVE OLE DB\IBM DB2 UDB FOR ISERIES IBMDA400 OLE DB PROVIDER, I can test the connection successfully. When I create a conn mgr from this data source (OLE DB Conn Mgr=data source name, Data Access mode = 'Table or View'), I get the "No tablesor views could be loaded." message where I should be specifying the table/view. If I change the Data Access mode to a SQL Command, I can enter a select statement (SELECT * FROM <Library>.<Table>), and get results. When I try to run my package with this for the destination conn mgr, I get the following errors... [OLE DB Destination [552]] Error: An OLE DB error has occurred. Error code: 0x80040E00. [DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (552) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0202009. If I go through the Advanced Editor for the OLE DB Destination object, I can edit the Access Mode to OPEN ROWSET, but thereis no combination of <Library> and/or <Table> that is acceptable... Attempt #3 I have set up a Data Flow object to return a recordset, and a ForEach object to cycle through them. Inside the ForEach loop, I've set up an Execute SQL Task to execute INSERT statements against an ODBC connection to the AS400. When I disable the INSERT command, the process runs fine, so I believe the rest of it is OK. When I enable the Execute SQL task, I get the following error: [Execute SQL Task] Error: Executing the query "INSERT INTO INTDTATST.IMSRSRP VALUES (?,?,?,?)" failed with the following error: "[Microsoft][ODBC Driver Manager] Invalid parameter number". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. I am pretty confident I have the recordset/variables setup correct, as I've done this somewhere else and it works (not using this ODBC connection, obviously)... If anyone has any ideas for any of these 3 scenarios, it would be much appreciated. TIA

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. :)

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

BJ, That isan 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.

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

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! --BJIf your settings pertain to SSIS configuration, then yes, please share so that we have it documented.

The configuration steps are as follows... 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 LISTis the AS/400 library USER ID should be populated PASSWORDshould be populated PERSIST SECURITY INFOshould 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 Iclick on the dropdown) Create a Connection Manager for the above data source 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 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...

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?

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

The information from BJ Custard helped a ton, but therewere 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!!!

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?

Yes, I did get it working. I made the field on the DB2 table a character field (type A) then I had no problem. You could try to get the date into a format like the link provided and that may also work. http://publib.boulder.ibm.com/iseries/v5r1/ic2924/books/c0925083182.htm#HDRDTIMES

Thanks for the information. I also was able to get it to work in a test environment by changing the TimeStamp field on Iseries to a date format, but I would really like to find a way to covert the sql date to Iseries TimeStamp

I also need to write to AS400 from SQL with SSIS without turning journaling off. Had anyone been successful doing this?

I am trying to call a stored proc on an AS400 from an SSIS package, and nothing yet seems to work when the stored proc being called has output or input/output parameters. I have tried using the SQL Command task as well as setting up a dataflow and using OLEDB Command object but neither works.Has anyone doneanything like this?

I am trying to call a stored proc on an AS400 from an SSIS package, and nothing yet seems to work when the stored proc being called has output or input/output parameters. I have tried using the SQL Command task as well as setting up a dataflow and using OLEDB Command object but neither works.Have you or anyone you knowdoneanything like this?

Has anyone figured out how to turn off the commitment control when using IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider with SSIS? Thanks

Hi, IBM has a solution http://www-1.ibm.com/support/docview.wss?uid=nas2a802b6eb29d32cac8625726c0041efc9 Jaimir G.

Did you get this resolved. Sounds like issue I am having. It's giving me the following and two of the fields are datetime fields(z) fields. Stacy

I am going the other way (IBM-2-SQL), but am having the exact same error, and after numerous re-installs of the IBM drivers (V5R3), uninstalling HIS, and general hairpulling, I still can't this to work.Can see the results if I preview the query, but as soon as I execute in the designer, it crashes with the OLE DB error and the DTS.Pipeline errors.Query runs fine in Showcase and through an OPENQUERY using a linked server setup using the same drivers, so I know the query is good. It has to be something in the configuration on my machine or in the package, and I cannot find it...Anybody any help???

I just spend some time on this same issue. It doesn't look like this thread has been updated in a while and from what I found ALOT of people are still hitting this problem.Symptoms Include1. Data previewing in BIDS (2005 or 2008) but not working at run time.2. Metadata now looking correct in BIDS.3. AS400 DB2 connections not working in Windows Server 2008 64 bit, but correctly in 2003Solution1. Use the IBM Iseries Data provider. The Microsoft DB2 provider gave me more issues.2. The iSeries Windows connection needs to be patched on the Windows server it is located on. The issue seems to be related to 64 bit connections if an old 2003 Server patch was installed. I may be wrong here but the patch fixed my issue.3. Get the latest patch here. Check your version in iSeries navigator and find the correct one if needed. You may need to create a login and account. http://www-03.ibm.com/systems/i/software/access/windows/sphist.html4. Install, and try again!Hope this helps some one in the future and saves them some time!Thanks,- Vimalwww.livelogic.net

Hi, I'm doing data migration from SQL Server 2008 to IBM AS400 in DB2. I managed to connect and insert data to DB2 but the transfer speed is very slow. 1000 records I need more than 3 minutes to transfer. May I know any other settings or configuration that we need to do at AS400 for this? I'm using Microsoft Oledb provider for Db2.

Double check your destination task. Make sure it is on "Fast Load". The default load is very slow.

Steve - you need an updated version of the provider. This new one (part of the SQL Server 2008 R2 Feature Pack - bing for it) allows you to do openrowset with fastload to DB2/400 (but not to DB2/MVS or DB2/LUW systems). The latter will come in a future release. CharlesCharles Ezzell - MSFT

Hey Charles, Is this only for the Microsoft OleDb Provider for Db2? I am using the IBM DB2 UDB FOR ISERIES IBMDA400 provider. Is there anyway to get have fast load work on this provider? If not is there a version of the Microsoft OleDb Provider for DB2 that allows fast load on 2005? Thanks for the help! Robert

Robert, The V3 provider i mention above should work with SQL 2005 without any problems. If there is a problem (doubtful, but possible), let me know. Also, multiple oledb destinations work very well when you have several million rows of data to upload, along with fastload.Charles Ezzell - MSFT

I am using the ISeries provider, but i have noticed a sizable increase (7-10 fold) by switching from using an OLE DB Destination to using an OLE DB Command (with a parameterized insert statement). Logically this doesn't make sense to me because I would think the OLE DB Destination is basically doing the same thing (without fastload turned on... which is unavailable using the ISeries provider). But never the less that is what i am seeing.

Can you please share the steps needed to upload a sql server table to as400 using ssis thanks

Hi , can you please help me by showing the steps taken to insert records in As400 ... I am having the journalling not on error.Please help. thanks

If DB2/400 is telling you that journaling is not enabled and needs to be enabled, you need to enable journaling. Nothing else you can do. Contact your DB2 Admin for help on that.Charles Ezzell - MSFT

I saw somewhere using IBMDASQL driver can bypass the journallng option . Can I please be guided by the steps needed to take to use that for an oledb destination ? I was able to create a table but cannot insert records as cannot see the table from the drop down. Please help. thanks

Hi, I have been playing with this for a while now and came to this thread because I just couldn't remember what the initial catalog was on the AS400. A senior moment, I guess. I figured this was as good a place as any to post what I have found. Here is a sample connection string for DB2OLEDB. This is only useable with the Enterprise or Developer editions of SQL Server. One thing you will want to do is go to the Advanced Editor in your data tasks and set Always Use Default Code Page to true on the Component Properties tab. This will prevent the 'Cannot retrieve the column code page info . . .' Warnings. The package would run without doing this, but the errors are annoying. Data Source=TESTING; User ID=USERNAME; Initial Catalog=DBNAME; Provider=DB2OLEDB; Persist Security Info=True; Network Address=TESTING; Package Collection=QGPL; Default Schema=LIBRARY; Default Qualifier=LIBRARY; DBMS Platform=DB2/AS400; The data source will be the system name or IP Address. I can get away with not specifying the Data Source, but that may just be my particular setup. The initial catalog is the database name and may be found in the Databases group in ISeries Navigator or by entering DSPRDBDIRE from an AS400 command line. On the systems I work with it is the same as the system name in live, but not in dev. I have persist Security Info as true to allow debugging the package. The network address must be specified and can be either the DNS or IP Address. I put the Package collection in the General Purpose Library (QGPL) just because it will be visible for all of the schemas. The Default Schema is similar to schemas in SQL server and is the Library name where your data resides. The Default Qualifer allows you to use select statements without specifying the library name in your sources and destinations like this 'Select fld1, fld2, from filename'. This is very useful if you are developing against a test environment because you will not have to change the statement when you move to live.

After DAYS of agonizing over this issue I finally found your instructions and they actually work. Journalling must be turned on in order to write to DB2 files, I have seen some posts that say this may not be necessary, however, it is. You can always turn it on for the actual write and then turn it off again if you don't want the overhead. In other posts (in other places) instructions say to put the IP address into the catalog library list - of course that does not work. Thank you for your clear instructions.L Buser

After DAYS of agonizing over this issue I finally found your instructions and they actually work. Journalling must be turned on in order to write to DB2 files, I have seen some posts that say this may not be necessary, however, it is. You can always turn it on for the actual write and then turn it off again if you don't want the overhead. In other posts (in other places) instructions say to put the IP address into the catalog library list - of course that does not work. Thank you for your clear instructions.L Buser