Loading images and text files in to database using Import Column Transformation

Import Column Transformation is used to add Text, image and xml content from files to a data flow stream. It allows users to read content available in flat files and loads it in to a column like Image, Text, NText and XML. Lets see an example on how to load content of images into to a database table using this component.

1. First search your computer and locate few images and copy them in to folder. I assume that you are copying files a.jpg, b.jpg, c.jpg and d.jpg in to folder ‘C:\InputImages’.

2. Then prepare a sample input file with the following content

ImageId, ImageFilePath

a.jpg, C:\InputImages\a.jpb
b.jpg, C:\InputImages\b.jpb
c.jpg, C:\InputImages\c.jpb
d.jpg, C:\InputImages\d.jpb

3. Drag and drop a Flat File Source in to a data flow task.

4. Configure the flat file source so that it points file which contains the above content(step 2).

5. Then drag and drop a Import Column Transformation in to the data flow and attach output of flat file source to Import Column transformation.

6. Now we have to configure the Import Column transformation. Double click on Import Column Component; it opens a dialog similar to the one below. Switch to InputColumns tab and select the column ImagePath as input column. We have to select a column which holds path of the images as input column, so that the Import Column transformation can read the files content and load it in to memory.

7. After configuring the input column, then switch to ‘Input and Output Properties’ tab to add an output column and link the output column to input column.

8. Browse and select the node ‘Import Column Output->Output Columns ‘ and click the Add Column button. Name the newly added column as ImageContent.

9. After adding the output column, remember value of ID property(in the above image it is shown as 49) and enter the value in FileDataColumnId property of input column(show in the image below)

10. After configuring Import Column Transformation, then Drag and drop an OLE DB Destination and attach output of Import Column transformation to OLE DB Destination

11. Make sure that your OLE DB Destination is going to point to a table which can hold ImageId and ImageContent and map the columns

12. That’s all your package is ready to import data available in flat files in to database tables