How to use the Import and Export Column Transforamtion in SSIS

How to use the Import and Export Column in SSIS:

Today I am going to demonstrate where and how to use the Import and export column transformation SSIS.

Before going to the demo first I want to explain the use of Import and Export column.

Import and Export columns are mainly used for grabbing of data which are like images, files (Docs, PDF etc…..) to any type of database and again if you want to move the same data to some other folder or somewhere Location in the file system.

It will store the data in encrypted format using the Import column and again it will easily decrypt the data to actual files using the export columns.

I Hope you got some idea on import and export column.

Let’s begin the demo on Export and Import Column Transformation. Follow the Below Steps..

Step1:

Create one table with below schema

CREATE TABLE [dbo].[Export](

      [ID] [int] IDENTITY(1,1) NOT NULL,

      [Location] [varchar](150) NULL,

      [FileContent] [varchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

This table is used for the storing of file location and file content through Export column.  

Step2:

After creating the table open the BIDS and create a Integration project and open a new Package.

Step3:

In this package drag and drop the Execute SQL Task from Control flow Items.

This is used for before loading the data into the table I am truncating the table and reloading the table with the data

And configure the Execute SQL Task like below

Connection: here give the connection string of your local database which is having the Export table.

SQLSource Type: Set the property to direct input

SQLStatement: TRUNCATE TABLE dbo.Export

After setting these three properties click on Ok.

Step4:

Now Drag the Foreach Loop Container to the Package.

This for getting the each file name or image name and location.

And configure the For each Loop Container like below.

After opening the For Each Loop Container go to the collection Tab and fill the all fields like shown in above.

In Folder field give the path of the files location which is going to store in the database table.

In Files keep *.* this means it will grab the any type of file.

In Retrieve file name select the fully qualified.

In the above image I am showing the path and files which are going to store in the database table using the Import Column.

Now go to the variable mapping tab and create one user defined variable at package level. And use that variable for storing the file location fully qualified name to the variable.

And keep the Index to 0, and then click on OK.

Step5:

Now Drag the Another Execute SQL Task to the For Each Loop Container for storing the Variable value to the Table.

Same way set the all the Properties like first Execute Task only change is SQL Statement

SQL Statement: Insert into dbo.Export(Location) Values(?)

 Then after this go to the Parameter Mapping tab in that tab choose the variable which is created at for each loop container.

And configure this tab as shown like in above image.

After this click on ok.

Step6:

After this drag the Data Flow Task to the package.

In this dataflow drag the Ole DB Source and select the Export table

After selecting the table click on ok.

Step7:

Now drag the Import Column Transformation to the Data Flow Task

After this configure the Import Column Tab

Go to Input columns tab and select the Location and then click on OK.

Step8:

Now drag the Ole DB Command component to the Data flow task.

In this tab set the connection to the database connection.

In this component tab set the few things

SQL Command: UPDATE dbo.Export SET FileContent = ? WHERE Id = ?

Now go to the next tab column mapping

In this u will found one new column that is File Location and Map the Id and File Location to Param_1 and Param_0 respectively.

Now click on ok.

Till here data is imported to the table.

Step9:

Now Data is ready at database level now we need store this data back to the Folder for that we need to use the Export column Transformation.

Here also drag the Ole DB Source to the Data Flow and configure the table to Export Table.

Next take the Derived Column to the data flow for change the path of the file to storing the data to the folder destination.

Here use the Replace for to change the Source to Destination then click the ok.

Step10:

Now drag the Export Column transformation to the Data flow and set the properties.

Here set the properties like

Extract Column to  the File content column

And File path to the Destination which is created at Derived column.

And check the force truncate.

Then click on ok and now run the package.

Before package running

After running the Package.

               

This is all about the Import and Export Column in SSIS.

If you are unable to see the images let me know. i will share u the file.