We have a DB2 10.5 system (on windows) that we are exporting to a SQL Server 2016 data warehouse on a daily basis using SSIS 2013. The issue I am running into is that some of the tables in DB2 have BLOB columns (picture data) and when I use SSIS to import that to SQL Server it is very slow. The main DB2 table has about 25,000 new records per day and it takes about 3 hours to move that from DB2 to SQL Server.
The main bottle neck is that SSIS has to write the BLOB to disk, then read it and store it in SQL Server. I am averaging about 130 rows per second with both systems on solid state drives.
My SSIS data flow is set up like so...
OLE DB Data Souce connected to DB2 10.5
Data Converison task to change DB2 BLOB datatype to Unicode Text Stream (DT_NTEXT)
Derived Column to fix DB2 date to SQL date issue
OLE DB Data Dest to SQL Server 2016
I had the SSIS BLOB storage on C:\temp at first and that was even slower (about 75 rows per second) and I read a blog about why not to do that. So I moved the BLOB to a different drive and that about doubled the speed but at 130 rows per second, it takes about 3 hours to process 25,000.
Is there any way to speed this up? Is there any way to use a memory location to read and write the BLOBs too?
Any suggestions would be much appreciated!