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

By:   |   Comments (24)   |   Related: 1 | 2 | 3 | 4 | More > Integration Services Development


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 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:
    Create 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:
    Add a Data Flow Task in SSIS
  3. Go to the Data Flow Tab and drag and drop the OLE DB Source and the Export Column task and join them:
    OLEBD and Export column task on the SSIS Data Flow
  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.

    OLE DB Source Editor with T-SQL code to SELECT the needed columns
  8. 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).
    Export column Transformation Editor
  9. Start the debug and 101 pictures will be saved.
    Debug SSIS project and generate the images written to the C:\images directory
  10. Verify that the pictures were stored in the c:\images folder
    Folder with images generated from SSIS
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 to read about the new FileTable feature tip.
  • Review the following resources for more information:


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, April 1, 2020 - 4:22:00 PM - Wakka Back To Top (85247)

This helped me a bunch.

Friday, August 9, 2019 - 10:29:49 AM - Adam Travers Back To Top (82014)

Hi,

Many thanks for this great article! Is there a way to export all of the images in a specific format (.jpg .bmp etc) 

Tuesday, November 7, 2017 - 9:27:51 AM - Tom Back To Top (69353)

Hi.

I am using ms SQL server 2014, visual studio 2017 and SSDT 2017 preview. When I try to extract pics from table, i receive this error:

TITLE: Microsoft Visual Studio
------------------------------

There was an error displaying the preview.

------------------------------
ADDITIONAL INFORMATION:

Conversion failed when converting the varchar value 'c:\images\' to data type int. (Microsoft SQL Server Native Client 11.0)

------------------------------
BUTTONS:

OK
------------------------------

 

Any Idea how to fix it?

Thanks for help

 

Tom

Wednesday, June 7, 2017 - 11:06:05 AM - Sabine Back To Top (56928)

 Hi,

 

Thanks. It's ok for image and binary format

 

Sabine

Thursday, September 8, 2016 - 8:19:45 AM - lukas Back To Top (43282)

Superb :-) Thank you

Monday, August 22, 2016 - 3:55:08 AM - ALAA Back To Top (43157)

 WHAT IF I WANT IT TO RUN FOR GIVEN UNIQUE IDENTIFIERS EACH TIME

 

Wednesday, June 15, 2016 - 6:26:01 AM - prem Back To Top (41698)

 

Works perfect but I  am getting the same issue as mentioned by Gary and RiK 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.

 

 

 

Does any one had come up with a solution. Thanks in advance 

Wednesday, October 21, 2015 - 1:06:46 PM - Vinny Back To Top (38952)

The Extract Column MUST be converted from a varchar or whatever to TEXT


IE: SELECT CAST([ColName] as TEXT) AS [ColName]

otherwise the column never shows up as an "Extract Column"

 

Just figured I would save someone the struggle.

 

Monday, October 5, 2015 - 7:52:31 AM - Alfred Back To Top (38823)

Very Very nice 

Monday, August 31, 2015 - 7:54:30 AM - Vadim Back To Top (38569)

Very nice tip

Friday, July 10, 2015 - 8:08:17 AM - Urs Steiger Back To Top (38170)

Hello Daniel

Your tip was very helpful, thank you very much. It is a simple solution for the export of data from a image field that works immediatitly and very fast. I wasn't able to export those data with the bcp command using a format file.

Cheers,

Urs

Sunday, March 29, 2015 - 10:58:10 AM - chandana Back To Top (36742)
  • *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS andthe code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
 
 
Very Clear Thank you.

Tuesday, March 3, 2015 - 2:59:36 AM - Bongani Back To Top (36412)

This article is very useful. For me it worked very perfect. Thanks for the article.

Wednesday, September 17, 2014 - 6:22:32 PM - Jamie Back To Top (34570)

Hi,

Sorry this process is not working for me in Server 2014.

I used this syntax:

"

declare @path varchar(100)= 'f:\docs\'

SELECT [Resume_Text, @path+[Candiate_Id] AS Path

FROM [CustomerDB].[Candidates].[Resume_Text]

"

Error:"Invalid Object Name 'CustomDB.Candidates.Resume_Text'.

 

Then I used this syntax:

"

declare @path varchar(100)= 'f:\docs\'

SELECT [Resume_Text, @path+[Candiate_Id] AS Path

FROM Candidates

"

Error:"Error Converting data type varchar to float'.

 

How I got to this point:
I have restored a BAK file from unknown Server Version into SQL Server 2014, everything seems present and correct.
The column I am trying to extract, I am unsure whether it has a file or not.

When I extract/query the specific "Resume_Text" column i get encoded data/blob "||BW||eJztPdt22ziSv4KZI+mecbK8SJQ... plus clear text content "To Whom it may concern, ...".
I want to keep files intact and then move them to the cloud.

The Column "Resume_Text" is Text not varchar???

But the encoded data makes me think it was once a file???

 DB called "CustomerDB"
"CustomerDB" has a table called "Candidates"
"Candidates" Table has a column called "Resume_Text"(Text, null)

Thoughts?
Any help would be much appreciated

 

Tuesday, March 25, 2014 - 5:36:59 PM - khalid Back To Top (29879)

i don't find the path in the export column.plz help me

Monday, January 27, 2014 - 9:14:48 AM - Dalila Back To Top (29233)

Thanks for the tip .. how would you do this for data type of "image" 

Friday, December 20, 2013 - 3:47:21 PM - Daniel Back To Top (27864)

Please check that the SSIS Service has permissions in that folder.

Friday, December 20, 2013 - 3:28:41 PM - pradeep Back To Top (27863)

Hi Daniel ,

You have wrote excellent artical and very useful.but I am getting Below error when i am exporting images from databse. in my  development sever has  images folder is there in C Drive

 

[Export Column [2]] Error: Opening the file "C:\images\" for writing failed. The path cannot be found.

[Export Column [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Export Column" failed because error code 0xC02090A0 occurred, and the error row disposition on "Export Column.Inputs[Export Column Input].Columns[Path]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
 
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Export Column" (2) failed with error code 0xC0209029 while processing input "Export Column Input" (3). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
 
But I have images folder in C drive.i dont no what i have to do. can you please le me know 
 
thanks
prad
 

Wednesday, October 9, 2013 - 10:39:25 AM - Rik Back To Top (27092)

Great post and I found it work really well with my own test database but I'm also getting a similar error to Gary on another SQL box (2005 SQL), has anyone found a solution to that issue yet? I've also tried using BCP and failed to get any meaningful data out of an IMAGE data type, on .doc files I've been able to export but the data is full of rubbish (as well as the actual data). 


Cheers,

Rik

Wednesday, September 11, 2013 - 9:58:31 PM - naimish Back To Top (26754)

Hi Daniel ,

You have wrote excellent artical and very useful. Actually, we have database with images about 200 GB. What is the best way to export this images to folder?

Please advise. Thanks and much appericiated.

Cheers,


Naimish

 

Monday, June 24, 2013 - 11:46:21 AM - Daniel Ruiz Back To Top (25541)

I found this useful and it worked for me. The question that I have in which I'm stuck. I want to include the exported image to an Excel file I need. How I can accomplish that? Thanks in advanced DR


Thursday, May 2, 2013 - 7:22:02 PM - Gary Back To Top (23691)

I did what you said in the instructions from a table where an application did the import into an "image" type field of a pdf file. I got the project to work but 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. Is there some trick to exporting the document that was import via an application to get it in a state I can open the file? If I import into the same field using a SQL statement I can then export and open the file with acrobat just fine. I can look inside the exported file with notepad++ and see there is content. I could send a sample of the exported file that cannot be opened if it would help. The application, when right clicking a field on a form says "add OLE object' and allows you to browse to a file to attach or import via the form within the application.

I sure would appreciate some help in getting the files out of the DB. I have been trying for a couple weeks now.

 

Saturday, April 20, 2013 - 2:21:21 PM - satheesh Back To Top (23456)

Thank You Nice article

Thursday, February 7, 2013 - 9:21:53 AM - Satish Back To Top (21975)

Thank you for useful Article  :)














get free sql tips
agree to terms