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!

Import multiple images to SQL Server using SSIS

MSSQLTips author Daniel Calbimonte By:   |   Read Comments (22)   |   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.
     
    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
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. 


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



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



Comments
Get free SQL tips:

*Enter Code refresh code


 
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.