We currently use SSIS for DB2/400 and MS SQL bi-directional integrations.
Only issue is that the DB2/400 ODBC, .NET, OLEDB drivers (we've tried them all) don't play nice with MS DTC transactions which is what SSIS uses to do transactional consistency (as400 folks sometimes call this commitment control I believe) so that it is generic to the database platform your are interacting with in SSIS. It's currently an open issue with IBM that we spent months back and forth with Microsoft and IBM on. Ended up being IBM wasn't doing things correctly with their ODBC driver and provided a fix for a specific version of Client Access. We ended up reverting to non-transactional integrations and things have been running just fine.
So as long as you don't need robust transactional consistency without hacks (the primary hack being running a BEGIN TRANSACTION at the beginning of the SSIS integration and COMMIT or ROLLBACK TRANSACTION statement at the end if things were successful or not) then SSIS is a good tool to use. A lot of people do the BEGIN / COMMIT transaction from what I gather (not specific to DB2, but other database platforms as well).
Interesting IBM doesn't bundle in a powerful ETL tool like Microsoft does with their database product.
Matt
-----Original Message-----
From: Crump, Mike [mailto:Mike.Crump@xxxxxxxxxxxxxxxx]
Sent: Monday, June 25, 2012 2:01 PM
To: Midrange Systems Technical Discussion
Subject: RE: ETL/ELT Tools
Thanks Craig. That is one that is currently on our list to look at.
Michael Crump
Security and Data Center Manager
Verallia
SGCI
1509 S. Macedonia Ave.
Muncie, IN 47302
765.741.7696
765.741.7012 f
www.verallia.com
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Craig Pelkie
Sent: Thursday, June 21, 2012 5:33 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: ETL/ELT Tools
SQL Server Integration Services?
It works well with the IBM i Access or MSFT OLE DB Providers (or ODBC).
Craig Pelkie
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Crump, Mike
Sent: Thursday, June 21, 2012 7:01 AM
To: Midrange Systems Technical Discussion
Subject: ETL/ELT Tools
We are looking at some ETL tool options.
1.) Source is SAP on MS/SQLServer
2.) Target is IBM DB2/400
3.) There will be other sources but the above is our primary concern -
the product has to play very well in the SAP arena and we would like to see some affinity or knowledge of interfacing with DB2/400. Most people tend to go the other way with BI/BW (IBM I -> MS SQLServer) but we have historically had great success with DB2/400 and the IBM i.
4.) We have no interest in DIY
5.) We have looked at open source TALEND but aren't quite happy with it.
6.) Historically we have used SPSS (Showcase) Warehouse Builder but
aren't quite comfortable with it in the SAP environment
7.) We believe that InfoSphere DataStage is too expensive for our tastes
(I'm trying to confirm that)
While others may not have our requirements (#1 and #2 specifically) I thought I would get them out there. Any opinions on experience with ETL/ELT types of tools would be appreciated.
Michael Crump
Security and Data Center Manager
Verallia
SGCI
1509 S. Macedonia Ave.
Muncie, IN 47302
765.741.7696
765.741.7012 f
www.verallia.com<
http://www.verallia.com/>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
