SSIS is a product that should be indifferent to whatever RDBMS you use. That’s also true. It will work with every RDBMS through drivers ODBC, OLE DB, ADO.NET etc. BUT it works best with Microsofts SQL-server.
This blogpost will take a look at using IBM DB2 with SSIS. Below is a test using ODBC-driver for IBM DB2 from IBM, OLE DB for IBM DB2 from Microsoft and an ADO.NET-driver.
Transfer rate of data
Transferring 100.000 rows from one IBM DB2-server to another IBM DB2-server.
| OLE DB-driver for IBM DB2 from Microsoft | 6 minuts |
| ADO.NET-driver | 30 seconds |
| ODBC-driver for IBM DB2 from IBM | 5 seconds |
As seen above the ODBC-driver for IBM DB2 from IBM is the best solution when it comes to transfer rate. The OLE DB-driver for IBM DB2 from Microsoft is a really poor choice. But using the ODBC-driver for DB2 from IBM is not the solution to everything!
Lookup-task in SSIS
As seen in the picture below it’s only possible to use an OLE DB-connection in the Lookup-task. The Lookup-task is really slow and it might be better doing it in SQL. Regarding a lot of the tasks in SSIS, they are really slow (see links below).
OLE DB Command
SSIS also contains a ‘OLE DB Command’-task and no ‘ODBC Command’-task.
Fast load/Bulk load
OLE DB
It’s NOT possible to use the fast load option with the OLE DB-driver when it comes to IBM DB2. It’s possible to choose the fastload option, but you will get the error below.
The reason why the OLE DB-driver is that slow is probably that it transfers one row at a time.
ODBC
It’s possible to use a batch/fast load load option in ODBC.
ADO.NET
It’s also possible to use a bulk/fast load option in ADO.NET.
Links
The links below is to different sources that can help you with SSIS.
SQL Server Integration Services Design Patterns
A book that describes different design pattern for SSIS.
Task Factory for SSIS from Pragmatic Works
A collection of optimized SSIS-tasks.
Cozyroc
A collection of optimized SSIS-tasks.
PragmaticWorks free online training videos
Free online training videos about SSIS (search for SSIS).
Pluralsight
Online training videos in SSIS (requires a paid subscription to Pluralsight).