Working with BLOB fields: Export Column and Import Column transformations

Resorting to BLOB fields as storage medium for physical files is a common need nowadays. SQL Server support BLOB data in two standard types as Image or Varbinary. SSIS also provides two transformations to work with BLOB fields: Export Column and Import Column.

Export Column will fetch physical files from BLOB data, whereas Import Column feeds the physical file to an Image or Varbinary field.

In this recipe we use both Export Column and Import Column transformations. With Export Column we fetch actual files from the Document table in the AdventureWorks2012 database. With Import Column, we load physical files into the Image type field of the database table.

Getting ready

Create a new empty file at C:\SSIS\Ch03_Data Flow Task-Part 2-Transformations\Files and name it R10_files.txt.

How to do it...

1. Create a new Integration Services project in BIDS and name the project R10_Export Column.

2. Create a Data Flow Task; in the Data Flow...