Execute T-SQL Statement Task in SSIS

In SSIS there is two way to execute the T-sql.

1. Execute SQL Task
2.  Execute T-SQL Statement Task

Execute T-SQL Statement Task

This task runs only T-sql statement. It takes less memory, parse time and CPU time than the Execute SQL task, but it is not flexible because we can neither parameterize the SQL nor save the results to a variable. Both components are performing same task. Now you are egger to know why SSIS is having two components to performing same task. Both having its own advantage and disadvantages. Let us see.

If we want 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.  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.

Step by step to learn how to use Execute T-SQL Statement Task

Open the SSDT.

Double click on the task.

 

Create the new connection.

Click ok.

Now we need to write the T-SQL statement. Generally we are using this task to insert some fix values in the table. Or need to use truncate statement because we cannot use variable in this task.

Click on ok.

 

Now package is ready to execute.

 

Package completed successfully.

Now I am seeing the result in SQL Server.

Row Count Transformation in SSIS

Suppose you want to find the number of row from flat file or table we need to write the custom code for the same. For finding the number of row of the table is bit easy but think about flat file. For that we need to write the custom code. It is somewhat complex task to make it easy SSIS provide a transformation know as Row Count.

The Row Count Transformation counts the number of rows that have passed through a Data Flow and puts that count into a variable.

Let’s learn step by step how to implement the row count transformation in SSIS

 Here I am taking one example and implementing row count. I am having a flat file and want to find the number of row in that flat file and want to print the count through script task.

Open the SSDT (Sql server data tool)

Take data flow task

 

Double click on that you will be redirected on data flow pan. Here I am taking flat file as the source.

Need to configure the connection manager for the flat file source.

Click ok.

Map the column.

Click ok.

Now we need to declare the variable on package label on that we need to store the row count value.

Right click on pan you will get the pop up on that you click on the variable

Declare the variable.

Now I am taking Row count transformation

  

Double click on the row count transformation. You will get the Row count prompt. Select the row count variable.

Note: In SSDT you will get this prompt. But if you are using BIDS you will get below prompt.

Now click on ok.

Now I am taking script task on control flow pan to display  the value of row count.

Double click on the Script task.

Select the Read only values from the user define variable as you see on above.

Click on ok.

Now click on Edit script task

Visual studio will be open

In main method we are writing our custom code.

In main method I am write code to display the value of row count.

C# Code

public void Main() { // TODO: Add your code here MessageBox.Show("Number of Row in flat file is :  +Dts.Variables["User::RowCount"].Value.ToString()); Dts.TaskResult = (int)ScriptResults.Success; }

Compile and Save it and close the visual studio.

 

Click ok.

Now it is read to run. I am running this package.

We will get the below result

 

Now see the number of record in flat file

Oh!!!! Getting expected result.

Hope this is helpful to you.

Enjoy  J

Keep tuning with us. 

Selection result as HTML in sql server

We can store the sql server in HTML using below sql query. Here I am using AdventureWorks2008R2 .

DECLARE @html nvarchar(max), @table nvarchar(max); SET @html = N'<html><head><title>Product List</title></head>' + CHAR(10) + N'<body style="font-family: Arial">' + N'<h2>Product List</h2>' + '<table border="1">' + N'<tr>' +     N'<th width="120">ProductID</th>' +     N'<th width="360">Name</th>' +     N'<th width="90">ProductNumber</th>' +    N'</tr>' + CHAR(10);  SELECT @table =        CONVERT(nvarchar(max),               (SELECT td = ProductID                       ,''                       ,td = Name                       ,''                       ,td = ProductNumber                       ,''                                      FROM [AdventureWorks2008R2].[Production].[Product]                 FOR XML PATH(N'tr'), TYPE));  SET @html = @html + @table + CHAR(10) +             N'</table></body></html>';  -- Final selection to return result. SELECT @html As [HTML Code];

We get the result in HTML code.

We can save the result in note pad and save it as HTML.

We can open it.  You can see it on browser.

  

Enjoy it.