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
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.