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 the data in this table, you'll notice that the FileName field in the table is a super-long file path. Modify that path so that it points to your directory "c:\exports\."

5. Add a Derived Column transformation task to the Data Flow design surface. Connect the output of the OLE DB Data to the task.

6. Create a Derived Column Name named "NewFilePath." Use the Derived Column setting of <add as new column>. To derive a new file name, just use the primary key for the file name and add your path to it. To do this, set the expression to the following:

    "c:\\exports\\" + (DT_WSTR,50)DocumentID + ".doc" 

   Note

   The "\\" is required in the expressions editor instead of "\" because of its use as an escape sequence.

7. Add an Export Column transformation task to the Data Flow design surface. Connect the output of the Derived Column task to the Export Column task. The Export Columns task will consume the input stream and separate all the fields into two usable categories: fields that can possibly be in digitized data formats, and fields that can possibly be used as file names. Figure 6-12 is a graphic that has been created to show the contents of both categories for this example.

   Note

   Notice that fields like the primary key [DocumentID] do not appear in either collection. This field doesn't contain embedded data, and it cannot be resolved to a file name.

8. Set the Extract Column equal to the [Document] field, since this contains the embedded MS Word object. Set the File Path Column equal to the field name [NewFilePath]. This field is the one that you derived in Derived Column task.

9. Check the Force Truncate option to rewrite the files if they exist. (This will allow you to run the package again without an error if the files already exist.)

10. Run the package and check the contents of the "c:\exports\" directory. You should see a list of MS Word files in sequence from 1 to 9. Open one and you'll be able to read the document in MS Word.

Figure 6-12