Exporting images with SSIS

Case
In a previous article I showed you how to import images (or other files) with SSIS into a SQL Server table. This article shows you how to get them out again with the Export Column Transformation.

Solution
This example  assumes that you have a filled table named ImageStore which I created in the previous article.

CREATE TABLE [dbo].[ImageStore](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [imageName] [nvarchar](50) NULL,
 [imageFile] [varbinary](max) NULL
) ON [PRIMARY]

1) Variables
Add a string variable to the package to store the path of the export folder.
I called mine ExportFolder. Fill it with a value like: C:\Users\Joost\Pictures\Export\

Variable to store the export folder

2) OLE DB Source
Add an OLE DB Source component that reads from the table ImageStore. We need the columns imageName and imageFile.

OLE DB Source component

3) Derived Column
With the folderpath from the variable and the filename from the column imageName we create an export filepath which is needed for the Export Column component. Name it NewFilePath.

Expression: @[User::ExportFolder] + imageName

4) Export Column
Add an Export Column component and select the imageFile column as Extract Column and the NewFilePath column as File Path Column.

Export Column

5) The result
Run the package and watch the folder for the result.

The result

Note: it also works with other filetypes, not just images.