Professional SQL Server™ 2005 Integration Services

6.4. Export Column

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. The trick to understanding the Export Column transformation is that it requires an input stream field that contains digitized document data and another field that can be used for a fully qualified path. The Export Column transformation will convert the digitized data into a physical file on the file system for each row in the input stream using the fully qualified path. In Chapter 4 you studied this transformation in detail, so in this chapter you'll just look at a quick example.

In this example, you'll use existing data in the AdventureWorks database to output some stored documents from the database back to file storage. The AdventureWorks database has a table named [production].[document] that contains a file path and a field containing an embedded Microsoft Word document. Pull these documents out of the database and save them into a directory on the file system.

1. Create a directory with an easy name like "c:\exports\" that you can use when exporting these documents.

2. Create a new SSIS package named Export Column Example. Add a Data Flow task to the Control Flow design surface.

3. On the Data Flow design surface, add an OLE DB Data Source configured to the AdventureWorks database table [production].[document].

4. If you preview ...