Import multiple images to SQL Server using SSIS

By:   |   Comments (26)   |   Related: 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:

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, July 26, 2017 - 10:56:57 AM - ram Back To Top (63285)


 hello daniel,

can we store images in one drive and text file in other drive

for example i stored my txt file in c drive containing path of images located in d drive

its throwing me an error 

 The "Import Column" failed because error code 0xC02090BB occurred,

please suggest

Friday, September 11, 2015 - 7:13:19 AM - Ravi Kumar Back To Top (38648)

Greeting for the day,

I have one Requirement, if you could help me i will be thank full to you.


I have some data on image(.jpeg/PNG or etc).

this image is stored in SharePoint server or in a Public Location.

i want to reed the data which is on the Image and need to insert into Some Tables in a database. 

first i need to know that is it possible to read data which is on Image using SSIS Packages.

using OCR Tools we can extract data and we can export it into excel, 

but using SSIS is it possible?

Friday, September 11, 2015 - 3:59:11 AM - HHob Back To Top (38647)

Im doing exactly the same Steps u r doing here but im getting this Error:

Error: 0xC02090BB at Data Flow Task, Import Column [9]: Opening the file "C:\Users\Images\4.jpg" for reading failed. The file was not found.


Knowing that: 1- The number of items in the list was found correctly .. Got a Green Color on Flat File Source 

                   2- The File "C:\Users\Images\4.jpg" is public and accessible.


Thanks in Advance :) 

Thursday, December 18, 2014 - 12:34:12 AM - Matish Kumar Back To Top (35653)

HI Daniel  ,,

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

Friday, July 4, 2014 - 5:30:13 PM - Ashish Bajpai Back To Top (32553)

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

You are GREAT......

Wednesday, February 20, 2013 - 8:41:26 PM - Farman Back To Top (22322)

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 Back To Top (21701)

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 4, 2012 - 3:44:43 PM - EDelVillar Back To Top (20743)


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 Back To Top (19312)


very good...

Thursday, August 30, 2012 - 12:26:55 AM - Daniel Calbimonte Back To Top (19298)

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

Wednesday, August 29, 2012 - 7:23:37 AM - Raghav Back To Top (19272)

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 Back To Top (19264)


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 3, 2012 - 8:36:31 AM - teja Back To Top (17252)



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 Back To Top (17194)

---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 Back To Top (17128)

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

Wednesday, April 25, 2012 - 10:49:49 PM - Vrishabh Back To Top (17117)

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 Back To Top (17113)

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

Tuesday, April 24, 2012 - 2:21:27 PM - mahesh Back To Top (17081)

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 Back To Top (17074)

Yes, the export column can be used. 

Tuesday, April 24, 2012 - 9:28:55 AM - mahesh Back To Top (17071)

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 Back To Top (16876)

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 Back To Top (16863)

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 Back To Top (16854)

Very good ....!!!

Wednesday, April 11, 2012 - 10:06:35 AM - Daniel Calbimonte Back To Top (16852)

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 Back To Top (16847)


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 Back To Top (16842)

Good One Daniel!!

get free sql tips
agree to terms