Import multiple images to SQL Server using SSIS

By: Daniel Calbimonte   |   Updated: 2012-04-11   |   Comments (26)   |   Related: 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

About the author

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