Learn more about SQL Server tools


Latest SQL Server Tips

Free SQL Server Learning

SQL Server Performance Monitoring in the Cloud

Monitor and Troubleshoot SQL Server Performance

5 ways monitoring SQL Server Agent Jobs can make your job easier

Deploying Applications in Azure without Configuration Limitations

Import multiple images to SQL Server using SSIS

MSSQLTips author Daniel Calbimonte By:   |   Read Comments (23)   |   Related Tips: More > Integration Services Data Flow Transformations

Sometimes we need to import thousands of images to SQL Server. This example shows how to import a list of images to SQL Server using SQL Server Integration Services.



  • SQL Server Enterprise or Standard (in this case I am using SQL Server 2008 R2, but it can work with SQL Server 2005 as well).
  • SSIS installed (it is included in the SQL Server installer).


  1. First of all, it is necessary to create a flat file named listImages.txt (or the name of your preference) with the paths of the images that you want to import to SQL Server such as the following:

  2. Create a table myImages with 3 columns: The ID is the primary key, image will store the picture and the path will store the image path:
    CREATE TABLE [dbo].[myImages](
     [id] [smallint] IDENTITY(1,1) NOT NULL,
     [path] [varchar](200) NULL,
     [image] [image] NULL,
     [id] ASC
  3. Start the SQL Server Business Intelligence Studio and create a New SQL Server Integration Project.
    The Business Intelligence Development Studio

  4. Drag and drop the Data Flow Task to the design pane.
    Drag and drop the Data flow

  5. In the design pane double click the Data Flow Task
  6. In the Data Flow tab drag and drop a Flat File Source, an Import Column and an OLE DB Destination.  Join the tasks with the green arrows as shown below.
    Create a Flat file, an import column and a OLEDB Destination
  7. The Flat File Source will connect to the listImages.txt created in step 1. Double click on the Flat File Source to edit the settings.
  8. In the Flat file connection manager, press New...
    Select the flat file

  9. In the Flat File Connection Manager Editor write a Connection manager name (any name can be used. In this example we will use imagefile).
  10. In the File name press Browse... and select the listImages.txt file created in step 1 (it can be stored anywhere. In this example it is in c:\images\)
    Define the flat file delimiters

  11. Select the Advanced options and in the Name, type Path to change the column name as shown below.
    change column name

  12. Then click OK on each window to save these settings.
  13. Next double click the Import Column transform and click the Input Columns tab as shown below.
  14. In the Input Columns tab check Name
    Check the name

  15. Click the Input and Output Properties
  16. Open the Import Column Output tree and select the Output Columns as shown below.
  17. Click the Add Column...
  18. button and name the new column Image
    Select the output column

  19. Get the ID property value of the column created (in this example the ID is 42).
    Verify the ID

  20. In the Input and Output Properties tab, open the Import Column Input > Input Columns and select Path as shown below.
  21. In the FileDataColumnID property write the ID from step 18 (in this example 42) and press OK to save these settings.
    Specify the ID

  22. Double click the OLE DB Destination.
  23. In the OLE DB Destination Editor Window, press New... for the OLE DB connection manager.
    Select the OLE BD Destination

  24. In the Configure OLE DB Connection Manager, press the New... button.
    Create the Data Connection

  25. In the Connection Manager, in the Provider combo box, select Native OLE DB\SQL Server Native Client.
  26. In the Server name write the SQL Server Name (in this example, the local server name is used which can be specified with a period).
  27. Select the Log on to the server information (in this example Windows Authentication is used).
  28. In the select or enter the database name, select the database used to create the table in step 2 (in this example, the Adventureworks database is used) and press OK.
    Select the server name and Database

  29. In the OLE DB Destination Editor name of the table or the view, select the table created in step 2 (in this example the table name is myImages).
    Select the table

  30. In the OLE DB Destination Editor, press the Mappings page and then press OK.
    Map the columns

  31. Now we are ready to start the project. Press the start debugging icon (green arrow). If everything is OK, the tasks should be colored green and the number of rows imported should be displayed.
    Run the package

  32. To verify that the data was imported successfully, open SQL Server Management Studio.
  33. Go to the database (in this example, Adventureworks) and open the myImages table.
    Verify the values


Next Steps
  • In this article, we imported multiple images at once using SQL Server Integration Services, see if you can follow these steps for your needs.
  • SSIS let's you import multiple images with the import column task, so keep this in mind the next time you need to import images.
  • Review the following tips and other resources:

Last Update: 4/11/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     

Thursday, December 18, 2014 - 12:34:12 AM - Matish Kumar Read The Tip

HI Daniel  ,,

   Tha a lot man ... and very good ...keep helping the ppl..thx 

Friday, July 04, 2014 - 5:30:13 PM - Ashish Bajpai Read The Tip

What I say.... Can express in words. However..... So Thanks.

You are GREAT......

Wednesday, February 20, 2013 - 8:41:26 PM - Farman Read The Tip

How do we add one more column to this, example Item. Getting a whole lot errors. 

Friday, January 25, 2013 - 12:10:37 AM - abc Read The Tip

I tried it there is no error occured during execution but when i run query in sql server management studio there is no data in image column.

Tuesday, December 04, 2012 - 3:44:43 PM - EDelVillar Read The Tip


I was getting an error message with listimage.txt I opened it with Programmer’s NotePad and was able to see that there were some special characters in the file and was able to delete them.  After that everything worked smoothly  

Thursday, August 30, 2012 - 8:10:32 AM - mahesh Read The Tip


very good...

Thursday, August 30, 2012 - 12:26:55 AM - Daniel Calbimonte Read The Tip

You should use the foreach loop in SSIS to get the files of the folder.

Wednesday, August 29, 2012 - 7:23:37 AM - Raghav Read The Tip

My requirement is ,just to automate the import of photos to the table on daily basis, only manual thing is HR to paste the phots to the directory and modify the listimage text file

Wednesday, August 29, 2012 - 5:17:32 AM - Raghav Read The Tip


I have tried your above steps and worked like magic.

I just tried by adding more photos to same listimage.txt, and run the project again, in table its showing duplicates + new phots imported

My requirement is ,just to automate the import of photos to the table?, only manual thing is paste the phots to the directory and modify the listimage text file


Thursday, May 03, 2012 - 8:36:31 AM - teja Read The Tip



i did't get the input columns in import column transform,will u please tell how to overcome this problem

Monday, April 30, 2012 - 12:01:27 AM - Ajeet Read The Tip

---Copy Image From Table to path(on disk)

  @File_Name VARCHAR(MAX) ,    
  @ObjectToken INT 
  SET @File_Name ='\\network drive/local drive'     
   SET @File_Name = @File_Name+CAST(@TRACK_ID AS VARCHAR(255))+'.jpg'
   PRINT @File_Name        
   PRINT @SQLIMG         
      EXEC sp_OACreate 'ADODB.Stream',@ObjectToken OUTPUT        
      EXEC sp_OASetProperty @ObjectToken, 'Type', 1        
      EXEC sp_OAMethod @ObjectToken, 'Open'       
      EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH        
      EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @File_Name, 2        
      EXEC sp_OAMethod @ObjectToken, 'Close'        
      EXEC sp_OADestroy @ObjectToken     
   SELECT @File_Name=''   

Thursday, April 26, 2012 - 9:45:37 AM - Daniel Calbimonte Read The Tip

I usually use the script tasks and file.getcreationtime to work with creation date:


Wednesday, April 25, 2012 - 10:49:49 PM - Vrishabh Read The Tip

Want to move files in source folder to destination folder on criteria -

Criteria - older than 6 months from today using SSIS packeage.(Based on date modified column in the folder)

Appreaciate for quick reply.......


Wednesday, April 25, 2012 - 5:27:26 PM - Daniel Calbimonte Read The Tip

Sure, we are working on it. We will send you tips shortly.

Tuesday, April 24, 2012 - 2:21:27 PM - mahesh Read The Tip

I am not able to export(images) to file path. please send me if you have example code or package...

Tuesday, April 24, 2012 - 10:44:18 AM - Daniel Calbimonte Read The Tip

Yes, the export column can be used. 

Tuesday, April 24, 2012 - 9:28:55 AM - mahesh Read The Tip

very good import process.  could you send/post image export (database to filesystem or c:\ path)


Thursday, April 12, 2012 - 10:56:55 AM - Daniel Calbimonte Read The Tip

You can combine both solutions using the foreach SSIS task to pass the file names as parameters and then run a T-SQL task using the parameter in combination with the select into clause.

Wednesday, April 11, 2012 - 11:50:41 PM - Ajeet Singh Read The Tip

Hi Daniel,

Tremendously it improves the performance.because I was using the same to transfer 80 GB image.,but is there any other approch using CLR/SSIS



Wednesday, April 11, 2012 - 12:12:30 PM - brahmareddy Read The Tip

Very good ....!!!

Wednesday, April 11, 2012 - 10:06:35 AM - Daniel Calbimonte Read The Tip

This sentence may help you to import faster:

INSERT INTO imalges(colImages) SELECT * FROM OPENROWSET(BULK N'C:\Imagen1.jpg', SINGLE_BLOB) as T1BULK N'C:\Imagen1.jpg', SINGLE_BLOB) as T1

Wednesday, April 11, 2012 - 7:19:44 AM - Ajeet Singh Read The Tip


Hi Daniel,

Is there any other faster method to import image from a directory to table?





Wednesday, April 11, 2012 - 1:44:49 AM - Dattatrey Sindol Read The Tip

Good One Daniel!!

Sponsor Information