Import Column Transformation in SSIS

Import Column Transformation in SSIS is used to import data from the text file to the data flow. And do some manipulations, and then forward the data to the destination. Here, data includes binary files, images, media, or any document which is vast to transfer. The data type of the output column must be DT_TEXT, DT_NTEXT, or DT_IMAGE.

For example, We are filling the Product details into the table. We have product pictures in the Manufacturing unit, and every individual store placed the product reviews in a text file. In this situation, we use SSIS import column transformation to bring the pictures, Reviews into data flow. Next, merge them with Product details and store them in Data Warehouse. Let’s see a straightforward example for better understanding

Import Column Transformation in SSIS Example

In this SSIS example, We will transfer a few images present in our local hard drive to the SQL Server Database column using SSIS Import Column Transformation.

TIP: Please refer Export Column Transformation in SSIS article to understand. It shows how to export images, binary files, media from SQL Server to file system.

For importing images into a database, we need a table that holds the image file and the path of the files. Let’s create a table named PHOTOSOURCE.

USE [SSIS Tutorials]
GO

CREATE TABLE PHOTOSOURCE
(
  PhotoID INT NOT NULL IDENTITY (1, 1),
  PhotoSource NVARCHAR (200),
  Photo IMAGE
)
GO
IMPORT COLUMN TRANSFORMATION IN SSIS 2014

Here we have some JPEG images and files called photo source, where it has all the image paths.

IMPORT COLUMN TRANSFORMATION IN SSIS 1

Configure Import Column Transformation in SSIS

STEP 1: Let start the BIDS and drag and drop the data flow task from the toolbox to the control flow. Next, rename it as Import Column Transformation.

IMPORT COLUMN TRANSFORMATION IN SSIS 2

Double click on it, and it will open the data flow tab.

STEP 2: Drag and drop Flat file source from the toolbox to the data flow region. Double click on Flat file source in the data flow region will open the Flat File Source Editor to select the text file data.

Since we did not create any flat file connection manager before, Click on New button to create one

IMPORT COLUMN TRANSFORMATION IN SSIS 3

Once you click on the New button, the Flat File Connection Manager Editor window will open to configure the connection manager.

Click on the Browse button to select the text files in the local hard drive.

IMPORT COLUMN TRANSFORMATION IN SSIS 4

From the above screenshot, you can observe that we are selecting the PHOTO SOURCE.txt file located in our local hard drive

IMPORT COLUMN TRANSFORMATION IN SSIS 5

From the above screenshot, you can see that we unchecked the Column names in the First data row option because we don’t have any column names in the first row

STEP 3: Click on the columns tab to verify the columns. Here we have only one column as you can see in below figure

IMPORT COLUMN TRANSFORMATION IN SSIS 6

Click ok and then drag and drop the Import column transformation in the SSIS toolbox to the data flow region.

STEP 4: Now drag the blue arrow from Flat File Source to Import Column Transformation to perform transformations on the source data. Double click on the SSIS Import Column Transform to configure it

IMPORT COLUMN TRANSFORMATION IN SSIS 7

STEP 5: On the first page, it means Component Properties Tab; you don’t need to do anything. If you want, you can change the name. Select the Input Column tab where you will see available source columns check the file path column.

IMPORT COLUMN TRANSFORMATION IN SSIS 8

STEP 6: Now select the Input column Tab. Import Column Input already set to the chosen source columns input.

IMPORT COLUMN TRANSFORMATION IN SSIS 9

From the above screenshot, you can observe that we renamed the column 0 name to Image Column

STEP 7: Now select the Input and Output Properties Tab of SSIS Import Column Transformation.

IMPORT COLUMN TRANSFORMATION IN SSIS 10

From the above screenshot, you can observe that Within the Import Column input, we have the input column (Image Column), but we don’t have any output column. So, Select Import Column Output and add one output column by clicking Add Column.

IMPORT COLUMN TRANSFORMATION IN SSIS 11

From the above screenshot, you can see that we created an output column with the name of Photo, and the data type is DT_IMAGE.

Select the LineageID of the newly created Output Column name Photo (i.e., 24). For you it may be different value don’t worry J

IMPORT COLUMN TRANSFORMATION IN SSIS 12

STEP 8: We have to do one crucial step for SSIS Import Column Transformation. We have to copy this LineageID 24 highlighted in the above screenshot. Paste that ID value into the FileDataColumnID property present in the Image Column (Import Column Input). Now press ok.

IMPORT COLUMN TRANSFORMATION IN SSIS 13

ExpectBOM(Byte-order mark): A BOM only expected if the data has the DT_NTEXT.

Now let’s configure the OLE DB destination, and set the connection to the database where you created a table called PHOTO SOURCE.

STEP 9: Drag and drop the OLE DB Destination from the toolbox to the data flow region. Next, drag the blue arrow from the Import Column Transformation to OLE DB Destination.

Now we have to provide the Server, database and table details of the destination. So double-click on the OLE DB Destination and provide the required information

IMPORT COLUMN TRANSFORMATION IN SSIS 14

From the above screenshot, you can observe that we selected the previously created table, i.e., PHOTOSOURCE table inside the [SSIS Tutorials] Database

STEP 10: Click on the mapping tab to map the input columns to the destination columns.

IMPORT COLUMN TRANSFORMATION IN SSIS 15
  • PhotoID is the identity column. So it will automatically be inserted.
  • Image Column contains the path of the image. So we are storing this path details in the Photosource Column
  • The photo is the images located in the local hard drive. So we are mapping it to the photo column (Image Data Type)

Now let’s execute the SSIS Import Column Transformation task.

IMPORT COLUMN TRANSFORMATION IN SSIS 16

The Import Column Transformation in SSIS task executed successfully.

Let’s open the SQL Server Management Studio. Next, write the below T-SQL Statement to check the result.

SELECT [PhotoID]
      ,[PhotoSource]
      ,[Photo]
 FROM [SSIS Tutorials].[dbo].[PHOTOSOURCE]
IMPORT COLUMN TRANSFORMATION IN SSIS 17