How to use Import column and Export column in SSIS 2008,2012

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

About these ads

3 thoughts on “How to use Import column and Export column in SSIS 2008,2012

  1. 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.

    Reply
  2. 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

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s