SSIS Import Blob from Oracle cutting off all blobs to 100 bytes

• wayne.mcdaniel

  SSCommitted

  Points: 1546

  More actions

  August 30, 2016 at 2:48 pm

  #316837

  We have a project that we are working on where we are trying to use SSIS to import data from Oracle 9 to SQL Server 2012.

  There is one table that contains about 800 gigs of BLOB data. The standard Microsoft Oracle importer tool was able to handle everything except that table.

  So we wrote a custom SSIS package to import from Oracle to SQL Server.

  One version has an OLEDB data source that says "Select * from OracleTable". Then we do a lookup to see if the ID already exists in SQL server, and if there is no match we write it to SQL server. This version works.

  When I want to get the new/modified records, I don't really want to pull all 4.5 million blob records across the network just to find the changed records. So we wrote a second version, that does an initial pass to just get the ID numbers it needs, and puts them in a temp table. Then it uses an identical OLEDB data source that says "Select T1.ID, T1.BlobData from OracleTable T1, OracleIDTable T2 Where T1.ID = T2.ID".

  When we run the modified version, it cuts off every blob to 100 bytes. We added a script task and a data view right after the OLE DB source to check the length of the blob, and we are reading it as 100 bytes.

  * I know that the blobs have more than 100 bytes, because the slow SSIS package reads them all, and the old production system still reads them from Oracle

  * I know that the Oracle driver and SSIS and SQL are working, because the slow SSIS package reads them all.

  Naturally we didn't find this until it copied 1.5 million records, all as 100 bytes each.

  I feel like 100 bytes is a pretty arbitrary amount, so I keep looking for the "truncate these blobs to 100 bytes" setting, but I haven't found it yet.

  Does anyone have any ideas on what might be causing SSIS to cut these off?

  Wayne