Update image column with the physical file with SSIS

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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.




FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply