Derived Column Transformation & Data Conversion Transformation
- It will allow us to create new columns based on some formula
- We can use existing columns / any function and build expression for new derived column
- Derived column can be added / replaced in place of existing
-
EXAMPLE
Pre-requisite
CREATE TABLE DerivedColumnDemoSource (ID int, FirstNamevarchar(10), LastName varchar(10), DOB Datetime, Expense int,Income int)
GO
INSERT INTO DerivedColumnDemoSource (ID, FirstName, LastName, DOB,Expense, Income)
VALUES
(1,'Nisarg','Kinariwala','10/09/1983', 100,200),
(2,'Megha','Shah','07/12/1986', 50,250),
(3,'Swara','Desai','01/01/2005', 300,50)
Go
CREATE TABLE DerivedColumnDemoDestination (FullName varchar(21),Age int, Saving int, InsertDate DATETIME)
Steps
1. Prepare Data Flow Task like this
2. Add Derived Columns with Expression. We can use system variables, columns from source, system functions, user variables.
3. Data conversion allows us to change the datatype, increase the length, etc..
4. do proper mapping in destination
SSIS Derived Columns Transformation
Introduction
The derived columns Transformation is used in the situation where we want some manipulation with existing columns data and store it in a separate column.
Case Study
We have table objects called tbl_STUDENTREC where we have Student Roll, Student First Name, Student Middle Name, Student Last Name, Marks1, Marks2, Marks3 etc. Now we have a destination table object called tbl_STUDENTDTLS which contains Student Roll, Student Name, Total Marks. So we have to make some manipulation with first table columns to get the desire result for second table.
For tbl_STUDENTDTLS
[tbl_STUDENTDTLS].[Student Name] = [tbl_STUDENTREC].[Student First Name] +
[tbl_STUDENTREC]. [Student Middle Name] +
[tbl_STUDENTREC].[Student Last Name]
[tbl_STUDENTDTLS].[ Total Marks] = [tbl_STUDENTREC].[Marks1] +
[tbl_STUDENTREC]. [Marks2] +
[tbl_STUDENTREC].[Marks3]
How to Configure the SSIS Package
Step-1 [ Create Source and Destination Table Objects ]
/*
We have table objects called tbl_STUDENTREC where we have Student Roll name, Student First Name,
Student Middle Name, Student Last Name, Marks1, Marks2, Marks3 etc. Now we have a destination table
object called tbl_STUDENTDTLS which contains Student Roll, Student Name, Total Marks. So we have to make
some manipulation with first table columns to get the desire result for second table.
*/
-- Creating Source Table Objects
IF OBJECT_ID(N'tbl_STUDENTREC', N'U') IS NOT NULL
BEGIN
DROP TABLE tbl_STUDENTREC;
END
GO
CREATE TABLE tbl_STUDENTREC
(STUDROLL INT NOT NULL PRIMARY KEY,
STUDFIRSTNAME VARCHAR(50) NOT NULL,
STUDMIDNAME VARCHAR(50) NULL,
STUDLASTNAME VARCHAR(50) NOT NULL,
MARKS1 DECIMAL(20,0) NOT NULL,
MARKS2 DECIMAL(20,0) NOT NULL,
MARKS3 DECIMAL(20,0) NOT NULL);
GO
-- Inserting Records in Source Table
INSERT INTO tbl_STUDENTREC
(STUDROLL, STUDFIRSTNAME, STUDMIDNAME, STUDLASTNAME,
MARKS1, MARKS2, MARKS3)
VALUES (1, 'Subal', 'Chandra', 'Das', 90.00, 67.00, 87.00),
(2, 'Subash', 'Chandra', 'Das', 87.00, 69.00, 37.00),
(3, 'Rajarshi', '', 'Roy Chowdhury', 90.00, 97.00, 67.00),
(4, 'Sarmila', 'Das', 'Chowdhury', 45.00, 47.00, 77.00),
(5, 'Kajal', '', 'Gupta', 20.00, 37.00, 47.00);
-- Disply Records From Source Table
SELECT STUDROLL, STUDFIRSTNAME, STUDMIDNAME,
STUDLASTNAME, MARKS1, MARKS2, MARKS3
FROM tbl_STUDENTREC;
STUDROLL STUDFIRSTNAME STUDMIDNAME STUDLASTNAME MARKS1 MARKS2 MARKS3
1 Subal Chandra Das 90 67 87
2 Subash Chandra Das 87 69 37
3 Rajarshi Roy Chowdhury 90 97 67
4 Sarmila Das Chowdhury 45 47 77
5 Kajal Gupta 20 37 47
-- Creating Destination Table Objects
IF OBJECT_ID(N'tbl_STUDENTDTLS', N'U') IS NOT NULL
BEGIN
DROP TABLE tbl_STUDENTDTLS;
END
GO
CREATE TABLE tbl_STUDENTDTLS
(STUDROLL INT NOT NULL PRIMARY KEY,
STUDFULLNAME VARCHAR(150) NOT NULL,
STUDTOTALMARKS DECIMAL(20,0) NOT NULL);
GO
Step-2 [ Data Flow ]
Step-3 [ OLE DB Source Editor Configuration ]
Step-4 [ Derived Columns Transform Editor Configuration ]
Step-5 [ Data Conversion Transform Editor Configuration ]
Step-6 [ OLE DB Destination Editor Configuration ]
Step-7 [ Run the Package ]
Step-8 [ Analyze the Output of SSIS Package ]
Hope you like it.
