Tuesday, August 18, 2015

Derived Column Transformation & Data Conversion Transformation

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.