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.

Preparations:

CREATE TABLE [Files](
 [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 http://asqlb.blogspot.com/2012/01/ssis-expressions-in-variablesdynamic.html
5-Run the package and check your table...