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.
