ssis sql 2000 image to 2008 varbinary Failed to retrieve long data for column

Question

Hi,

I have a task that to migrate the image type column from sql 2000 to varbinary type in sql 2008.

The source column having 3812353 max datalength size for the column. The package always failed with following error message.

[OLE DB Source [13177]] Error: Failed to retrieve long data for column "attch_file_content_t".

[OLE DB Source [13177]] Error: There was an error with output column "attch_file_content_t" (13209) on output "OLE DB Source Output" (13187). The column status returned was: "DBSTATUS_UNAVAILABLE".

[OLE DB Source [13177]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "attch_file_content_t" (13209)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "attch_file_content_t" (13209)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Answer 1

It basically was the connection error.

i manage to solve the connection issue on one table but now found another issue on another table.

The initial intention is migrate the source data to staging. The source are sql 2000, they have some attachment as an image type Then we have the Failed to retrieve long data for column issue when we just do the select * from tableA

Then I try to do the select cast(ImageTypeColumn) as varbinary(8000) from tableA now it work for those data which is not exist 8000 byte

But unfortunately in the another table, there exist some rows data length is huge. Thus it failed again when we do the select cast(ImageTypeColumn) as varbinary(8000) from tableA

In Microsoft SQL Server 2000 and earlier versions, the varbinary data type had a maximum limit of 8,000 bytes. To store up to 2 GB of binary data the image data type needs to be used instead.

Answer 2

Finally i found a solution on that. Using ADO.net Source instead of using the OLEDB.net Source