Example of Execute SQL task

We have gone through regarding Execute SQL task in my previous post. Here is the example of Execute SQL Task in SSIS with various type of result set having SQLSourceType = Direct input: -

First create the table

Now configure the connection manager.

Now Edit the Execute SQL Task

1)    When ResultSet = None & SQLSourceType = Direct input

Mention the SQL statement in SQLStatement.

Now click on OK.

Execute SQL task will appear without cross sign as below:-

Now right click à Execute Task àafter execution Execute SQL task will look below :-

Validate the data in table

2)     When ResultSet = Single Row & SQLSourceType = Direct input

Declare a variable to hold the result having the same data type.

Here we are getting the count of rows in table so get the variable of data type INT32.

Modify the SQL Statement as below :-

Now modify the mapping of Result Set tab to hold the result as below :-

Now execute “Execute SQL Task”, RIGHT CLICK à Execute Task

3)     When ResultSet = Full result set & SQLSourceType = Direct input

Declare a variable of data type Object as we are going to hold full result set.

In Result set tab, the result binding name (for Result Name) must be set to zero for full result set and XML results, as below: -

Then Right Click à Execute Task

Note:-

1)    When Result set type = Single row then data type of variable can be any type that is compatible with the type column in the result set.

2)    When Result set type = Full result set then data type of variable will be ‘object’.

3)    When Result set type = XML then data type can be either String or Object.