Professional SQL Server™ 2005 Integration Services

6.2. Import Column

The Import Column transform was named the File Inserter transform in the beta releases of SQL Server 2005, and it is a partner to the Export Column transform, which was renamed from File Extractor. After discovering what they do, you may find that the former names are more intuitive. These transforms do the work of translating physical files from system file storage paths into database blob-type fields and vice versa. The trick to understanding the Import Column transform is that its input source requires at least one column that is the fully qualified path to the file you are going to store in the database, and you need a destination column name for the output of the resulting blob and file path string. This transform also has to be configured using the Advanced Editor—something you've only briefly looked at in earlier chapters. The Advanced Editor is not intuitive, nor wizard-like in appearance, hence the name "Advanced," which you will incidentally be once you figure it out. In the editor, you won't have the ability to merge two incoming column sources into the full file path, so if your source data for the file paths have the file name separate from the file path, you should use the Merge transforms to concatenate the columns before connecting that stream to the Import Column transform.

6.2.1. Import Column Example

Now you'll do an example where you'll import some images into your AdventureWorks database. Create a new SSIS project named "Import Column Example." ...