SSIS Import Failed on Long (Blob) Data

Posted: November 3, 2011 in BI, SQL Server 2005, SSIS

The Problem

A data import failed with the following error messages:

  • Failed to retrieve long data for column “Description”.
  • There was an error with output “OLE DB Source Output” (742) on component “OLE DB Source” (732). The column status returned was: “DBSTATUS_UNAVAILABLE”.
  • SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “output “OLE DB Source Output” (742)” failed because error code 0xC0209071 occurred, and the error row disposition on “component “OLE DB Source” (732)” 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.
  • SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component “OLE DB Source” (732) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

The column description is of the data type VarChar(Max). This error will normally occur when the data type is some form of BLOB

The Solution

When SSIS Imports BLOB data it writes the data our to a file and then imports the file. By default it will use the system temp directory for this. However you can set two directory locations for Blob data and system cache. It is recommended that you use fast disks and that these two directories are on separate disks.

Click on the Data Flow Task and view the properties. You need to enter values for two options: SSISBlobTempStoragePath and BufferTempStoragePath. I created directories with those names on the E and L drives.

Blob Path Config Setting

After making this change the data task ran in 5 minutes rather than 8. More importantly it shouldn’t fail in the future

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s