Importing Image into SQL Server with SSIS

Importing Image into SQL Server with SSIS

Written by Kasim Wirama, MCDBA, MVP SQL Server

This article will show you how to importing image into SQL Server with SSIS. Component relating to this job is Import Column component. It will extract file from file system based on full path of the file, and do importing job into table. Of course, the destination table should have LOB type (nvarchar(max), varchar(max), image).

You can try it out with these steps I show you below:

1. Create SSIS type project with Visual Studio, named Import Column.
2. Then you create image directory, let say : C:\images
3. Create some images in the folder (1.jpg, 2.jpg, 3.jpg)
4. Create text file (list.txt) inside the folder. Insert content into it below :

C:\images\1.jpg

C:\images\2.jpg

C:\images\3.jpg

1. You create table inside AdventureWorks database with table creation script below :

CREATE TABLE ImportImage

(

Picture image

);

1. Get back to SSIS project that you have created in step 1, make sure you get focus to Control Flow tab, drag Data Flow Task into the Control Flow designer window, then double clik the task to switch focus to Data Flow tab.
2. Drag Flat File Source into Data Flow designer, and double click to configure its properties to point to the flat file created at step 4.
3. At Flat File Source Editor, click New button, to create new connection manager. Specify your connection manager name and browse your text file by clicking Browse button or directly fill in into File Name text box.
4. After that, you click Columns item that is located inside left list box at Flat File Connection Manager Editor window to make sure content of the text file is loaded properly.
5. Close all remaining window to return back to Data Flow Designer, now you have created your source text file. Next you drag Import Column component into Data Flow designer window, and connect green arrow of the Flat File Source that you have just configured in previous step to the Import Column component.
6. Double click Import Column component to configure its properties so that it can import images into destination (table ImportImage at AdventureWorks database), that will be specified at step 17.
7. In Advanced Editor for Import Column, check for column and then move to Input and Output Properties.
8. At left list box, expand all item below Import Column Input, you will see column that you have checked in step 12. The column name may vary depending how you name it.
9. expand all item below Import Column Output, you see no column defined under it, click Add Column button, name it Picture, look its properties and make sure DataType is image, and note its LineageID
10. return back to input column that you see at step 13, find property named FileDataColumnID, and fill it with LineageID that you look at step 14.
11. Click all OK button to return back to Data Flow designer, drag OLEDB destination, double click on it to configure its properties.
12. Create new OLEDB connection manager to point to your AdventureWorks database. Choose data access mode : Table or view. And select your table that is created on step 5.
13. Move to Mappings item on left list box, and make sure there is relationship between Picture column (from Available Input Columns) to Picture column (from Available Destination Columns).
14. Click OK button to return back to Data Flow designer. And Press F5 to execute the package.

You will see green on each of the component (source – import column – destination) with 3 rows flow between each of them.

You can open SQL Server Management Studio, and focus to Adventureworks, and check whether any picture content exists in ImportImage table. It should have 3 rows in the table.

Happy exploring in SSIS world. J

Share this post: | | | |