Update image column with the physical file with SSIS

Problem:

you have a table with 2 fields:

FilePath -> this field contains address of local image file path

FileImage -> this field contains the image exactly, type of this field is image(sql server database)

Now you want to update FileImage field with files that are exists in exact local paths.

can you do it with SSIS? How?

Yes,

Use Data Flow Task , Import Column Transformation , OLE DB Command tranformation

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.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.