I have started a series on Exploring SSIS where I will explain various features available. In this post, we will see the usage of Execute SQL task in Control flow

Execute SQL task is primarily used to execute SQL statements and stored procedures with or without parameters. Let us see how we can execute simple select statement by passing a parameter

Open Business Intelligence Development Studio (BIDS). Navigate to File-->New-->Project. Under templates choose Integration Services Project. Give project and choose the location to save the project

In the solution explorer click on Pacakge.dtsx and you will see Tabs for Control Flow, data flow etc. Drag Execute SQL task item into the Control flow area. Name the task as Simple select

Double click on this task, you will see Execute SQL Task editor, under general section, choose connection type as OLE DB, choose the datasource for connection, choose the result set as Full Result set; choose SQLSourceType as Direct Input and write the following query in SQLStatement

select * from INFORMATION_SCHEMA.COLUMNS
where table_name=?

Now we need to create the parameter to supply the value for column table_name. Now right click on Control Flow area and choose variables. Create a new variable named table_name, choose datatype as string and type test as value. The purpose is to return all rows from information_schema.columns for the table named test. We need to assign the result to another variable. Create another variable named result_set, choose datatype as object and type 0 as value

We need to map these variables in the Execute SQL task. Double click on it and choose parameter mapping. Click the button Add and choose the variable named User::table_name, Direction -->Input, Datatype-->varchar; parameter_name-->0; parameter_size to -1. Goto resultset and choose the user::result_set for variable name and type 0 as result name

Now right click on Execute SQL task and choose Execute task. You see that everything gets executed well and you can see green color around the Execute SQL task.

Please refer these screenshots for better understanding

You may be also interested to read this