Thursday, February 23, 2012

SSIS iterate to update a blob column

A recent question in EE about how to update a blob column using SSIS, so I decided to create a blog on it as it's a lot of steps and I'll need to send hi a lot of screen shots.

You have file names such as 04-191-388086.jpg & 04-191-388087.jpg and the 388086 & 388087 corresponds to the ID column on that table and you need to update these 2 IDs.


 [ID] [int] NOT NULL,
 [FilePath] [varbinary](max) NULL)
Insert test records with no filePath... 

INSERT INTO [Files](ID) VALUES (388086), (388087)

So I thought about using 3 variables:

1-String to hold the file name and path.
2-Int to hold the ID, An Expression of the FileNameLocation variable.
3-String to hold the update statement, and EXPRESSION using the ID and the FileNameLocation variable (Optional you could use a direct T-SQL in the execute SQL Task)

I've added a value to the FileNameLocation or SSIS will yell and gives you errors.

And constructed my package as follow:

1-Foreach Loop Container to iterate over the files.
2-Execute SQL Task to run the update statement.

The steps:

1-will configure the Foreach Loop Container to iterate over the folder and get me the file names into the STRING variable "FileNameLocation".

2-Configure the Execute SQL Task to use my database connection, and the Query variable to update the table.

3- Add that expression* on the ID variable, to shred the ID out of the file name.
Reverse(Substring(Reverse(right(@[User::StrFileNameLocation],  FINDSTRING( REVERSE( @[User::StrFileNameLocation] ), "-",1) -1 ) ) ,5,20))
4-Add that expression* on the StrUpdate variable, which will be used as our query.
"Update Files set FilePath =( SELECT * FROM OPENROWSET (BULK '"+ @[User::StrFileNameLocation] +"',  SINGLE_BLOB) FilePath) where ID =" + @[User::IntID]
To add expressions to the variables, check that post
5-Run the package and check your table...


  1. Hey Jason, If I want the expression to use a string in the Where clause : "Update Files set FilePath =( SELECT * FROM OPENROWSET (BULK '"+ @[User::StrFileNameLocation] +"', SINGLE_BLOB) FilePath) where ID =" + @[User::IntID]
    Instead of an integer for IntID at the end, how do I do that? I assumed it was like this but its throwing an error
    '" + @[User::IntID] + "'

    I think I will be all set after this. You've been a big help on EE and great blog too!!

  2. Just wrap the IntID into extra single quote

    "Update Files set FilePath =( SELECT * FROM OPENROWSET (BULK '"+ @[User::StrFileNameLocation] +"', SINGLE_BLOB) FilePath) where ID ='" + @[User::IntID]+ "'"

  3. FileName =
    "C:\Integration Services\swatches\04-187-308436.jpg"

    I actually have to parse 2 more variables out of the file name and then use them in the update query in the Where clause. I know how to extract them using SQL but using them in the expressions is new to me. I have to take the 04 and the 187 out of the Filename and use them in the Where clause for the Update query. Id like to use the FINDSTRING like you did on the FilName above. Could you help me out on this. I found out the same fabric ID can exist but with different Div(04) and Season(187)

    1. if you can do it in the negine, it's even better for performance, bring it as a calculated column with the row. if you can't let me know and i'll work an expression for you. thanks