Monday, 1 August 2011

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.

5 comments:

  1. Thanks .. very informative. I tried a sample & working fine.

    ReplyDelete
  2. That was a great article. It really helped me. Thank you :)

    ReplyDelete
  3. Thanks alot. You made the task very simple.

    ReplyDelete
  4. Great Article. Thanks a lot..:)

    ReplyDelete
  5. Works perfect but I am getting the issue when I try and open an attachment that was exported with acrobat it tells me there is a error opening the file, that it was not decoded properly or was corrupted. The same pdf can be opened by using the application.

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...