Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

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

MSSQLTips author Daniel Calbimonte By:   |   Read Comments (11)   |   Related Tips: 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:


Last Update: 8/20/2012


About the author
MSSQLTips author Daniel Calbimonte
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, September 17, 2014 - 6:22:32 PM - Jamie Read The Tip

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 Read The Tip

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


Monday, January 27, 2014 - 9:14:48 AM - Dalila Read The Tip

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


Friday, December 20, 2013 - 3:47:21 PM - Daniel Read The Tip

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


Friday, December 20, 2013 - 3:28:41 PM - pradeep Read The Tip

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 09, 2013 - 10:39:25 AM - Rik Read The Tip

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 Read The Tip

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 Read The Tip

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 02, 2013 - 7:22:02 PM - Gary Read The Tip

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 Read The Tip

Thank You Nice article


Thursday, February 07, 2013 - 9:21:53 AM - Satish Read The Tip

Thank you for useful Article  :)




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.