Export images from a SQL Server Table to a Folder with SSIS

Problem

I have seen a previous tip that explained how to import multiple images to SQL Server. This was a great tip, but now my question is: is it possible to do the opposite?  Can I export images from SQL Server to a file in Windows?  What SQL Server options are available to do so?  Check out this tip to learn more.

Solution

Very simply, the answer is “yes”.  In this tip let’s use SQL Server Integration Services to export images from SQL Server to the c:\images folder. In this tip, I will outline the requirements and step by step process to export images with SQL Server Integration Services in order for you to learn and duplicate the process.

Requirements

• I am using the SQL Server 2012 with the SQL Server Integration Services installed. This example should work in SQL Server 2008 as well.
• In this example, I will use the AdventureWorks 2012 database, but an earlier version of the AdventureWorks database should have the same information.
• An “images” folder was created on the c:\ drive.

Getting started

In this example, we are going to copy the images stored in the AdventureWorks2012 database using the [AdventureWorks2012].[Production].[ProductPhoto] table which already contains some photos and export them to the c:\images folder.

1. Let’s start with the SQL Server Data Tools (SSDT) in SQL Server 2012 or the Business Intelligence Development Studio (BIDS) in SQL 2008 and open an Integration Services Project:
   
2. In the control flow tab drag and drop the Data Flow Task from the SSIS Toolbox to the design area:
   
3. Go to the Data Flow Tab and drag and drop the OLE DB Source and the Export Column task and join them:
   
4. Double click in the OLEDB Data Source.
5. In this example, we are going to connect to the Adventureworks2012 database and the Production.Photo table. If you do not have a connection created, press the New button and create a connection to SQL Server and the Adventureworks2012 Database (older Adventureworks databases also contain the production.photo table).
6. In the Database Access Mode, select SQL Command.
7. In the SQL command text, write the following code:

   declare @path varchar(100)= 'c:\images\'

SELECT [ThumbNailPhoto]
,@path+[ThumbnailPhotoFileName] AS Path
FROM [AdventureWorks2012].[Production].[ProductPhoto]

The query includes the picture (ThumbNailPhoto) and the path. In the variable @path we are using the “c:\images\” folder. The query is concatenating the path plus the filename.

• Double click in the Export Column Task and select the Extract Column (the name of the column with the images) and the File Path column (the path where you want to save the image).
  
• Start the debug and 101 pictures will be saved.
  
• Verify that the pictures were stored in the c:\images folder
  

Next Steps

• The Export Column task is a pretty easy tool to export data from SQL Server to a file. Basically, it requires the image column and the path in another column.
• If you are working with files and documents with databases, I strongly recommend you read about the new FileTable feature tip.
• Review the following resources for more information:
  • Export column transformation.
  • FileStream overview

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.

• MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018