Uploading the Image into sql server using SSIS

 Recently I got the requirement from the client to load the CSV data (including string and Image). The CSV file contains some fields and it also has an image file path. We need to load that that in the repo table. Once data is loaded in Repo it will be used in the different portals.

Let’s see how to achieve this.

Below is the file.    

We need to read this file and load this data into the database.

Before creating a package I created a table name “Product” in the database.

CREATE TABLE Product   ( ID int,  Name VARCHAR(50),  Img varbinary(max)   )

 Table created successfully.  

Now I am package.

Taking Data flow task.   

Double click on this.

 Taking source as a flat file and configuring the CSV file.

Now we are taking the Import column transformation.

   

Double click on this transformation.

In the Input The column we are selecting the column name which is having the file path which we want to load in the database.  

Here we are having the file path on the Image_Path Column.

Now we need to configure the Input and Output Properties. This Configuration is Important.

 Each column has unique LineageID.  For the Image_Path column, LineageID is 57. See below.     

Now we are creating the output Column

  

Clicking the Add Column  

Creating the output column named Image which LineageID is 95. And data type of this column is DT_IMAGE.

Now come to the input column in the Custom Properties.

We need to set the LineageID of the output column. In my case Output column, LineageID is 95.

See below.    

Click ok.

Now we need to map the output destination.

  

See the table.   

Now running the package.

Package executed successfully.  

See the records in the table.

  

Image loaded successfully in the database.

In the next post, we will see how to export the image from database.