Update image column with the physical file with SSIS

I will explain details on this example:

1- Create a table in sql server database with this structure:

CREATE TABLE [dbo].[Images](
    [FilePath] [varchar](500) NOT NULL,
    [FileImage] [image] NULL
)

as I said before,

FilePath contains local path of files

and Document must contain images files exactly.

2- Create a SSIS folder in c:\ , and copy 3 images there, rename them as 1.jpg , 2.jpg, 3.jpg

3- Insert 3 records in Images table with this data:

FilePath                                           FileImage
-------------------------------------------------- -----------
C:\SSIS\1.jpg                                      NULL
C:\SSIS\2.jpg                                      NULL
C:\SSIS\3.jpg                                      NULL

4- Create a SSIS package, Add a Data Flow Task, Add an OLE DB Source and fetch data from this table.

5- Add an Import column transformation

6- double click on import column transformation , Advanced Editor for Import Column window will open. go to Input columns tab, and tick FilePath Column.

7- go to Input and Output properties tab, under Import column output , under output column add new column and name it as myImage. Note that data type of this column must be DT_IMAGE .

8- copy ID property of myImage column, now go under Import column input, under input column, select FilePath. paste the copied id value in FileDataColumnID value here. and hit ok.

9- Add an OLE DB Command after Import Column.

10- double click on it, in first tab select connection manager same as oledb source connection manager.

11- in Component properties tab, in SqlCommand type this statement :

update Images set FileImage=? where FilePath=?

12- in column mapping tab, set these mappings:

myImage ----> Param_0

FilePath -----> Param_1

and then hit ok.

13- Run the package.

In above example, Import Column will read image files from local path and add them as new column to data flow. OLE DB Command is responsible for update FileImage columns with the images files from the column which comes from import column.