Hi
in this demo we will learn what are Import Column and Export Column Transformations in SSIS
Import Column :
MSDN says:
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
USE [SQLJUNKIESHARE]
GO
/****** Object: Table [dbo].[file] Script Date: 1/25/2012 7:04:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
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]
GO
SET ANSI_PADDING OFF
GO
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
Sqljunkieshare 
I followed the step as mentioned here but I am getting error on Execute SQL task as
“[Execute SQL Task] Error: Executing the query “declare @location varchar(100) = ? ;
if not exist…” failed with the following error: “Syntax error, permission violation, or other nonspecific error”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
”
Please help mi to find out the solution. Please.
use this query–
declare @location varchar(100)=?
if not exists(select 1 from [dbo].[file] where location =@location)
begin
insert into [dbo].[file] (location) values(@location)
end
Nice article.done without any error.