How to export image / blob-type data from SQL Server to File system using Export Column in SSIS

The Export Column Transformation is a transformation that exports data to a file from the Data Flow. The Export Column Transformation Task is used to extract blob-type data from fields in a database and create files in their original formats to be stored in a file system or viewed by a format viewer, such as Microsoft Word or Microsoft Paint.

In the following example, you’ll use existing data in the AdventureWorksDW database to output some stored documents from the database back to file storage. DimProduct table has a column called  LargePhoto which is varbinary (max).

Create a simple package something similar to this

Create a directory with an easy name like C:\demo\SSIS\Export that you can use when exporting these pictures.

Create a Derived Column Name named FilePath

Set the Extract Column equal to the [LargePhoto] field, since this contains the embedded GIF image. Set the File Path Column equal to the field name [FilePath]. Check the Force Truncate option to rewrite the files if they exist.

One you run, all the images will be export into the target location.

Cheers! Uma