Import multiple images to SQL Server using SSIS

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

Problem

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.

Solution

Requirements

• 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).

Demonstration

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:

   C:\images\pic1.jpg
   C:\images\pic2.jpg
   C:\images\pic3.jpg

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,
    CONSTRAINT [PK_myImages] PRIMARY KEY CLUSTERED 
   (
    [id] ASC
   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
   IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
   ON [PRIMARY]
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
   GO

3. Start the SQL Server Business Intelligence Studio and create a New SQL Server Integration Project.
    
   
   
   
4. Drag and drop the Data Flow Task to the design pane.
    
   
   
   
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.
    
   
   
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...
    
   
   
   
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\)
     
    
    
    
11. Select the Advanced options and in the Name, type Path to change the column name as shown below.
     
    
    
    
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
     
    
    
    
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...
button and name the new column Image
 



18. Get the ID property value of the column created (in this example the ID is 42).
     
    
    
    
19. In the Input and Output Properties tab, open the Import Column Input > Input Columns and select Path as shown below.
20. In the FileDataColumnID property write the ID from step 18 (in this example 42) and press OK to save these settings.
     
    
    
    
21. Double click the OLE DB Destination.
22. In the OLE DB Destination Editor Window, press New... for the OLE DB connection manager.
     
    
    
    
23. In the Configure OLE DB Connection Manager, press the New... button.
     
    
    
    
24. In the Connection Manager, in the Provider combo box, select Native OLE DB\SQL Server Native Client.
25. 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).
26. Select the Log on to the server information (in this example Windows Authentication is used).
27. 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.
     
    
    
    
28. 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).
     
    
    
    
29. In the OLE DB Destination Editor, press the Mappings page and then press OK.
     
    
    
    
30. 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.
     
    
    
    
31. To verify that the data was imported successfully, open SQL Server Management Studio.
32. Go to the database (in this example, Adventureworks) and open the myImages table.
     
    
    

 

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:
  • SSIS import column tasks
  • The import column transformation

Last Update: 4/11/2012

About the author

Daniel is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips

We Recommend

• More Business Intelligence Tips...

Comments and Feedback:

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

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? Regards Ajeet

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 - 12:12:30 PM - brahmareddy	Read The Tip
Very good ....!!!

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 Regards Ajeet

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.

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)

Tuesday, April 24, 2012 - 10:44:18 AM - Daniel Calbimonte	Read The Tip
Yes, the export column can be used.

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...

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.

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....... Thanks

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: http://msdn.microsoft.com/en-us/library/system.io.file.getcreationtime.aspx

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

Read The Tip

---Copy Image From Table to path(on disk) DECLARE   @SQLIMG VARCHAR(MAX),       @IMG_PATH VARBINARY(MAX),    @TRACK_ID INT ,   @TRACK_NO  VARCHAR(255) ,   @File_Name VARCHAR(MAX) ,       @ObjectToken INT  DECLARE IMGPATH CURSOR FAST_FORWARD FOR            SELECT  IMAGE_DATA,TRACK_ID ,TRACK_NO FROM TABLE_NAME   --where SDD_DOC_PATH='AjeetPhoto.JPG'   OPEN IMGPATH     FETCH NEXT FROM IMGPATH INTO @IMG_PATH ,@TRACK_ID ,@TRACK_NO   WHILE @@FETCH_STATUS = 0       BEGIN     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=''      FETCH NEXT FROM IMGPATH INTO @IMG_PATH ,@TRACK_ID ,@TRACK_NO      END     CLOSE IMGPATH   DEALLOCATE IMGPATH

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

Wednesday, August 29, 2012 - 5:17:32 AM - Raghav	Read The Tip
Daniel, 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 Raghav

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

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.

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

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

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.

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.

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! SQL Developer Bundle: Cut out dull work with 12 tools for simpler, faster database development. Free trial Need SQL Server help and not sure where to turn? Reach out to expert consultants in the USA for a Health Check. In two mouse clicks view SQL bottlenecks. With ZERO impact pinpoint all poor performing SQL with 100% accuracy. Optimizing SQL Server performance can be a daunting task. Or is it?