Using import column transformation data can be loaded from file
system task (content of a file) to database or any other destination adapter. For example:
Let's
assume we have a table named document. We want to load content of file to
vcData column according path specified in other column (vcDocumentName).
CREATE TABLE
tblDocument (
ntDocumentID BIGINT
,vcDoucumentName
VARCHAR(100)
,vcData NVARCHAR(MAX)
)
INSERT INTO
tblDocument
VALUES
(1,'Math',NULL)
,(2,'Che',NULL)
,(3,'Phy',NULL)
Step 1:
Drag any data source adapter in data flow task area. In this example it is
an ADO .NET source adapter and connected with Sql server:
![]() |
Import column source Adapter |
Step 2: It
is optional step. In this step we are going to concatenate actual physical file
path with the column named vcDoucumentName by
using derived column transformation:
![]() |
Import column transformation Physical Path |
Step 3: Drag
import column transformation and connect with derived column transformation.
Double click on it which will pop up advance editor.
![]() |
SSIS Import column input file path |
In Input
columns tab select column which keeps file path.
Step 4: Now
click on input and output properties tab. In the output Columns node add a new
column. In this example it is vcFileData and selecting DT_NTEXT as a data type
from drop down. Copy the LinenageID which is 297 in this example.
![]() |
Getting file content Import Column SSIS |
Step 5: In
input columns node, select vcDcoumntName (columns which keeps file path) and enter
the lineage ID of the output column in FileDataColumnID as shown in below
diagram:
![]() |
Linking File Path to File Data SSIS Import Column |
Step 6: Drag
OLEDB command and connect with import column transformation. Create a new OLEDB
connection. Double click on OLEDB command transformation. Under the connection
managers tab select new OLED connection from drop down.
![]() |
Updating File data SSIS Import Column |
Step 7:
Under Component Properties tab enter SQL Command:
UPDATE tblDocument
SET vcData =
?
WHERE ntDocumentID = ?
As shown in below diagram:
![]() |
Update query to Update with file data SSIS Import column |
Step 8:
Under column mapping tab, map the destination column with input column.
![]() |
Column Mapping Import column transformation SSIS |
Step 9: Now
execute the package!!
![]() |
Run Import column Transformation SSIS |
And check
your table:
ntDocumentID
|
vcDoucumentName
|
vcData
|
1
|
Math
|
I am learning mathematics.
|
2
|
Che
|
I
am learning chemistry.
|
3
|
Phy
|
I am learning physics.
|
1 comment:
That’s a very good article posted by the blogger and I think it was very good for beginners like me since I recently learned SQL and wanted to properly learn the functions of JOIN and UNION. I also have a good post for SSIS Insert or Update functions.
SSIS Upsert
Post a Comment