Wednesday, August 19, 2015

Import Column Transformation

SSIS Import Image

Introduction

Sometimes it is needed to import the Image to our MS SQL Server table object. In this article I am trying to discuss about the importing image by SSIS.

Case study
Before going to farther discussion please read the case study carefully.
I have a flat file named "SourceMVPFile.txt" which contains the records of 4 MVP holders. The flat file is comma separated. The records contain MVP holder name and a Picture path of each MVP holder.

There is a folder named "ProficPic" located at path "F:\Practice_SQL\SSIS" which contains the JPG picture file of each MVP holder.

We have to Extract data from flat file and Picture form Picture folder and load it to our MS SQL Server destination table object called "tbl_SQLMVPList". That's all.

So let's start it

Step-1 [ The Source flat file and Picture folder ]





Step-2 [ Create the Destination Table Object ]

-- The Destination table to Insert the Picture
IF OBJECT_ID(N'tbl_SQLMVPLIST', N'U') IS NOT NULL
   BEGIN
     DROP TABLE tbl_SQLMVPLIST;
   END
GO  
CREATE TABLE tbl_SQLMVPLIST
       (IDNO            INT               NOT NULL IDENTITY PRIMARYKEY,
        MVPNAME         VARCHAR(50) NOT NULL,
        PROFPICPATH VARCHAR(50) NOT NULL,
        PROFPICTURE IMAGE       NOT NULL);
GO       

Step-3 [ SSIS Data Flow Tasks ]



Step-4 [ Flat File Source Editor ]





Step-5 [  Advance Editor for Import Columns ]







Step-6 [ OLE DB Destination Editor  and Run the Package and See The Result ]

Nothing new here. Just configure it.




Hope you like it