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 sort of document which is huge to transfer. The data type of the output column must be DT_TEXT, DT_NTEXT or DT_IMAGE.
TIP: Please refer Export Column Transformation in SSIS article to understand. This will show you, How to export images, binary files, media or any sort of large documents from SQL Server to file system.
For example, We are filling the Product details into the table and we have product pictures in the Manufacturing unit and every individual store placed the product reviews in text file. In this situations, We use SSIS import column transformation to bring the pictures, Reviews into data flow to merge them with Product details and store them in Data Warehouse. Let’s see one simple example for better understanding
Import Column Transformation in SSIS Example
In this example, We will transfer few images present in our local hard drive to SQL Server Database column using SSIS Import Column Transformation.
For importing images into a database, we need a table that hold the image file and the path of the files. Let’s create a table named as PHOTOSOURCE.
USE [SSIS Tutorials] GO CREATE TABLE PHOTOSOURCE ( PhotoID INT NOT NULL IDENTITY (1, 1), PhotoSource NVARCHAR (200), Photo IMAGE ) GO
Here we have some JPEG images and file called photo source, where it has all the image paths.
STEP 1: Let start the BIDS and drag and drop the data flow task from the toolbox to control flow. Next, rename it as Import Column Transformation.
Double click on it and it will open the data flow tab.
STEP 2: Drag and drop Flat file source from toolbox to 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
Once you click on New button Flat File Connection Manager Editor window will be opened to configure the connection manager.
Click on the Browse button to select the text files in local hard drive.
From the above screenshot you can observe that we are selecting the PHOTO SOURCE.txt file located in our local hard drive
From the above screenshot you can observe 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 columns tab to verify the columns. Here we have only one column as you can see in below figure
Click ok and then drag and drop the Import column transformation in SSIS toolbox to 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
STEP 5: In the first page, it means Component Properties Tab you don’t need to do anything, if you want you can change the name. Select Input Column tab where you will see source available columns check the file path column.
STEP 6: Now select Input column Tab. Import Column Input is already set to the selected source columns input.
From the above screenshot you can observe that, We renamed the column 0 name to Image Column
STEP 7: Now select Input and Output Properties Tab.
From the above screenshot you can observe that, With in 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.
From the above screenshot you can observe 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
STEP 8: We have to do one important step here. We have to copy this LineageID 24 highlighted in above screenshot. Paste that ID value into the FileDataColumnID property present in the Image Column (Import Column Input). Now press ok.
ExpectBOM(Byte-order mark): A BOM is 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 toolbox to data flow region. Next, drag the blue arrow from the Import Column Transformation to OLE DB Destination.
Now we have to provide Server, database and table details of the destination. So double-click on the OLE DB Destination and provide the required information
From the above screenshot you can observe that, We selected the previously created table i.e., PHOTOSOURCE table inside the [SSIS Tutorials] data base
STEP 10: Click on the mapping tab to map the input columns to the destination columns.
- PhotoID is the identity column so it will automatically inserted.
- Image Column contains the images path so we are storing this path details in the Photosource Column
- 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.
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]
Thank you for Visiting Our Blog