SSIS: FILESTREAM Data Export And Import By Using SSIS Package


Exporting FILESTREAM (varbinary (max)) data through SSIS package from source database to FLAT FILE destination  and then loading from FLAT FILE to destination database, was a time consuming task for ME, at least. Let’s check out how I had resolved it.
 (Note: Reader must have initial information of package creation using Sql Server Integration Services)
Source/destination table is “image”, with only two columns, image_id as uniqueidentifier and image_file VARBINARY(MAX) FILESTREAM.
To extract data, I have OLE DB source, Data Conversion and Flat File Destination. Image_OLE is OLE DB source with simple select query. “SELECT image_id, image_file FROM dbo.image”. Here we need to convert image_file column as follow:
For destination select a Flat File Destination  and make following changes on “General” and “Advance” tabs
Advance Tab:
Datatype of image_file must be image[DT_IMAGE]. Here you are finished with Data Extraction. Execute this package and data will be exported to a text file at your desired location.
Let’s create one more package which will extract data from FLAT FILE source to an OLE DB destination, as follow:
Lookup is used to avoid duplicate row insertion. You can skip it. “Images” is FLAT FILE SOURCE with following necessary changes.
And on “Advance” tab
Datatype should be “Unicode text stream [DT_NTEXT]”. Use data conversion, to convert image_file data type to DT_IMAGE.
In the end provide OLE DB destination. Save your package and Execute to shift VARBINARY(MAX) data from text file to your desired database table.
Advertisements

Posted on December 8, 2010, in SSIS. Bookmark the permalink. Leave a comment.

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

%d bloggers like this: