in this demo we will learn what are Import Column and Export Column Transformations in SSIS
Import Column :
The Import Column transformation reads data from files and adds the data to columns in a data flow. Using this transformation, a package can add text and images stored in separate files to a data flow
The data type of the output column must be DT_TEXT, DT_NTEXT, or DT_IMAGE.
So Import Column transformation reads a file from the file system any file (.jpeg,.doc,.pdf….) converts to into text stream and adds to the data flow a column ,
Export Column Transformation:
Export column does in exactly reverse to the Import column transformation
he Export Column transformation reads data in a data flow and inserts the data into a file. For example, if the data flow contains product information, such as a picture of each product, you could use the Export Column transformation to save the images to files.
You can configure the Export Column transformation in the following ways:
- Specify the data columns and the columns that contain the path of files to which to write the data.
- Specify whether the data-insertion operation appends or truncates existing files.
- Specify whether a byte-order mark (BOM) is written to the file.Lets Understand both transformations in detail using a simple example
/****** Object: Table [dbo].[file] Script Date: 1/25/2012 7:04:55 PM ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[file](
[id] [int] IDENTITY(1,1) NOT NULL,
[location] [varchar](100) NOT NULL,
[textfile] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET ANSI_PADDING OFF
So first we will use a FOR EACH LOOP CONTAINER with FILE ENUMERATOR to loop all the files in the source and insert the file locations in the DBO.FILE table
so lets execute package so far see the results
result of the import column
Now lets export text file column to a file location using export column transformation
drag and drop a data flow task and add a oledb source same as import column with table as file
so now we configured the export column transformation lets run
before running the destination look like this
Above post is explained by me in this video