SSIS – Benefits of Execute T-SQL Statement Task

Introduction

In this article I am going to demonstrate the Execute T-SQL Statement task of SSIS. We all know the power of Execute SQL task in SSIS and from Microsoft SQL Server SSIS 2012 they introduce another Task named Execute T-SQL Statement task. Most of the developer has miss concept about this task. It has a definite purpose and that’s the reason Microsoft put it down in SSIS 2012.

Hope it will be informative and you enjoy the Session.

First of all we want to mention it clearly that whatever work we can perform in Execute T-SQL Statement task can be performed by the Execute SQL task also.  

But Execute T-SQL Statement task is build for different parlous that we mentioned later in this article. First we understand the Scenario what we want to do by Execute T-SQL Statement task.

The Scenario

The Execute T-SQL Statement task only performs any T-SQL operation that we needed in the data base.

We have a table named [dbo].[tbl_Employee]

EmpId	EmpName	EmpGrade
1	Joydeep Das	A
2	Deepasree Das	B
3	Sukamal Jana	C
4	Amit Jain	A

There are another table named [dbo].[tbl_EmployeeSal]

EmpId	EmpSal	YrEndBon
1	10000.00	2000.00
2	8000.00	1000.00
3	5000.00	700.00
4	11000.00	2000.00

Now the management decides that only the Employee with Grade “A” got the bonus this year and others are not and we have to fill another table named [dbo].[tbl_EmployeeFinalSal]

Te output should be like this

EmpId	EmpName	EmpGrade	EmpTotalSal
1	Joydeep Das	A	12000.00
4	Amit Jain	A	13000.00

Hope you understand the Scenario

How We DO That

Step – 1 [ Create the Base Table ]

IF OBJECT_ID(N'[dbo].[tbl_Employee]', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_Employee];

   END

GO

CREATE TABLE [dbo].[tbl_Employee]

 (

   EmpId    INT         NOT NULL IDENTITY PRIMARY KEY,

   EmpName  VARCHAR(50) NOT NULL,

   EmpGrade CHAR(1)     NOT NULL

 )

GO

INSERT INTO [dbo].[tbl_Employee]

  (EmpName, EmpGrade)

VALUES('Joydeep Das', 'A'),

      ('Deepasree Das', 'B'),

      ('Sukamal Jana', 'C'),

      ('Amit Jain', 'A');

GO

IF OBJECT_ID(N'[dbo].[tbl_EmployeeSal]', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_Employee];

   END

GO

CREATE TABLE [dbo].[tbl_EmployeeSal]

 (

   EmpId    INT           NOT NULL IDENTITY PRIMARY KEY,

   EmpSal   NUMERIC(18,2) NOT NULL,

   YrEndBon NUMERIC(18,2) NOT NULL,

 )

GO

INSERT INTO [dbo].[tbl_EmployeeSal]

  (EmpSal, YrEndBon)

VALUES(10000, 2000),

      (8000, 1000),

      (5000, 700),

      (11000, 2000);

GO

-- Table to Fill as Output --

IF OBJECT_ID(N'[dbo].[tbl_EmployeeFinalSal]', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EmployeeFinalSal];

   END

GO

CREATE TABLE [dbo].[tbl_EmployeeFinalSal]

 (

   EmpId     INT            NOT NULL PRIMARY KEY,

   EmpName   VARCHAR(50)    NOT NULL,

   EmpGrade  CHAR(1)        NOT NULL,

   EmpTotSal NUMERIC(18,2)  NULL

 )

GO

Step – 2 [ The Control Flow of Package ]

Step – 3 [ The Execute T-SQL Statement Task ]

The T-SQL Statement

BEGIN

    SET NOCOUNT ON;

    TRUNCATE TABLE [dbo].[tbl_EmployeeFinalSal];

      IF EXISTS(SELECT * FROM [dbo].[tbl_Employee] WHERE EmpGrade='A')

         BEGIN

              INSERT INTO [dbo].[tbl_EmployeeFinalSal]

                         (EmpId, EmpName, EmpGrade)

                  SELECT EmpId, EmpName, EmpGrade

                  FROM   [dbo].[tbl_Employee] WHERE EmpGrade='A';

                  UPDATE b

                         SET b.EmpTotSal = a.EmpSal + a.YrEndBon

                  FROM   [dbo].[tbl_EmployeeSal] AS a

                         INNER JOIN [dbo].[tbl_EmployeeFinalSal] AS b

                               ON a.EmpId = b.EmpId

                  WHERE  b.EmpGrade = 'A'

         END

END

If we look at the connection we find that the Execute T-SQL Statement takes the ADO.NET connectivity.

Step – 4 [ Run and Observe the Output ]

SELECT * FROM [dbo].[tbl_EmployeeFinalSal];

Output:

EmpId   EmpName           EmpGrade          EmpTotSal

1              Joydeep Das        A                          12000.00

4              Amit Jain            A                           13000.00

Some Fats About Execute T-SQL Statement task

In compare to Execute SQL task the Execute T-SQL Statement task tasks less memory, parse time, and CPU time. If we need to run parameterized queries, save the query results to variables, or use property expressions, we should use the Execute SQL task instead of the Execute T-SQL Statement task.  

Also, the Execute T-SQL Statement task supports only the Transact-SQL version of the SQL language and you cannot use this task to run statements on servers that use other dialects of the SQL language.  In addition, the Execute SQL task supports many connection types but the Execute T-SQL Statement task supports only ADO.NET.  So in the end, if you want a bit more speed and don’t need the additional flexibility, use the Execute T-SQL Statement task over the Execute SQL task.

References:

 http://www.jamesserra.com/archive/2012/11/ssis-execute-sql-task-vs-execute-t-sql-statement-task/

Other important properties

Hope you like it.

Posted by: MR. JOYDEEP DAS