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.

Posted by: MR. JOYDEEP DAS