move images in physical directory to sql server image type

Question

How do I use SSIS to iterate the image files in a directory and using the filename run a query to insert the image into sql server?

I realise that with a Foreach File Enumerator I can loop the files and get the filename into a variable. How do I use this variable to run a query to find the record for that filename from hd in my table and then import the image into my sql server image type column?

Once I have the file in my database, I will delete the file from hd.

Answer 1

If I'm understanding the problem correctly, you would like to sweep all the files in some location into SQL Server using SSIS?

Data Flow Task

Your data flow task will be responsible for the actual import of files into the database. Your approach would be the same as outlined in Import varbinary data Pretty picture version at insert XML file in SQL via SSIS

Your source will be a Script Transformation Component operating as a source component. It's job will be to add all the file names into the Data Flow. Change the filter in the second link to *.png (or whatever your filter is) and it should work.

Use the Import Column Component on the generated file names. This will add the file pointer into the data flow so that it can get imported into the database. You will need to ensure your data type is DT_IMAGE. Even if you're using varbinary(max)/varchar(max)/nvarchar(max) it's all going to be DT_IMAGE within the context of the pipeline's metadata.

Route all of that data into your target table and you will have imported your file data.

File cleanup

At this point, you have imported all this data and now you want to remove the files from disk. Assuming you stored the file name in the database along with the image bits, I'd use an Execute SQL Task to retrieve the list of file names. Change the output type from None to Full Result Set and store that into a variable of type Object.

Connect a Foreach Enumerator to the output of the SQL Task and here you'll want to "shred" the results. Google that term and you'll find a variety of blog posts or previous SO questions on how to do this. The end result will be a file name will be pulled from the recordset object and assigned to a local variable.

Inside the Foreach Enumerator, use a File System Task and Delete the file which is referenced in the variable set from the Foreach Enumerator.