Sunday, March 17, 2013

Import Images to Sql Server using SSIS

In this article, I am going to demonstrate all the steps to create a package in Sql Server Integration Services to import Images in Sql Server.

1. Create a flat file named ListImages.txt containing list of paths of images to be imported in Sql Server

Example (ListImages.txt)
C:\Images\Image1.gif
C:\Images\Image2.gif
2. Create a table in database to copy the images into Sql Server

CREATE TABLE dbo.tblImages(
    id int IDENTITY(1,1) NOT NULL PRIMARY KEY
    , imgPath varchar(200) NULL
    , image image NULL
 )

3. Start the SQL Server Business Intelligence Studio and create a New SQL Server Integration Project "ImportImages"


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


5. In the design pane Right Click on Data Flow Task and then click Edit or double click the Data Flow Task


6. From 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. Right Click on Flat File Source and then click Edit...


8In the Flat file connection manager, press New...


9. In the General option, mention Connection Manager Name, Description (Optional) and browse flat file ListImages.txt created in Step 1


10. Select Advance option and change the name to ImagePath. Click Ok twice to save the settings.


11. Right Click on Import Column and then click Edit... In the new window, select Input Columns tab and select Name


12. Select Input and Output Properties Tab and expand Import Column Output from the TreeView. Select Output Columns and click on Add ColumnGet the ID property value of the column created. (In this example, ID is 58)


13. Expand Import Column Input from the TreeView and select ImagePath. In Column Properties, set FileDataColumnID value to 58 (generated in step 12) and Click Ok to save the settings


14. Right Click on OLE DB Destination and then click Edit


15. In the OLE DB Destination Editor Window, press New... for the OLE DB connection manager.


16. In the Configure OLE DB Connection Manager press the New... button.


17. In the Connection Manager, in Provider combo box, select Native OLE DB\SQL Server Native Client and provide other details like Server Name, Authentication Type, Credentials and Database Name and Click Ok.


18. In the OLE DB Destination Editor Window, In Connection Manager Option select the table tblImages.


19. Select the option Mapping and do the mapping as shown below and Click Ok.


20. At this point, we are done with creating the package and ready to run the project. If everything is OK, all the tasks should be displayed with green color and the number of rows imported should be displayed.


21. To verify the data is imported successfully, Open Sql Server Management Studio and connect the database and open the table tblImages


4 comments:

  1. its good but i am getting error in OLE DB Destination "Truncation may occur due to inserting data from data flow column "Image Path" with a length of 255..."

    ReplyDelete
    Replies
    1. I believe the size of actual image path would be greater than the size of column "ImagePath" in database table. Please increase the size of "Imagepath" in the table and try once again.

      Delete
  2. Yes later i found that my notepad was empty. Now it is solved...really nice post..

    ReplyDelete