Saturday, January 2, 2016

SSIS - Execute SQL Task

The Execute SQL Task is the one of the most important part of SQL Server Integration Services in the Control Flow window because if some information need to pass or get from the database in the package then this control flow item comes into the process. It runs SQL statements or stored procedures from a package. The task can contain either a single SQL statement or multiple SQL statements that run sequentially. 
Another thing is that you can use TRUNCATE, SELECT, INSERT, UPDATE, and DELETE commands frequently include WHERE clauses to specify filters within this control.

The most interesting facts about SQL queries and stored procedures are that, they are frequently use input parameters, output parameters, and return codes. The Execute SQL task supports the InputOutput, and ReturnValue parameter types.

Importance of the Execute SQL Task: In the package, we can use the Execute SQL task for the following purposes:
  1. Truncate a table or view in preparation for inserting data.
  2. Create, alter, and drop database objects such as tables and views.
  3. Re-create fact and dimension tables before loading data into them.
  4. Run stored procedures with parameters also - In the Execute SQL Task, make sure SQLSourceType is set to Direct Input, then your SQL Statement is the name of the stored procedure, with question marks(?) for each parameter of the procedure.
  5. Save the rowset returned from a query into a variable.
Configuration of the Execute SQL Task: By using very simple steps, we can configure the Execute SQL task in the following ways:
  1. Specify the type of connection manager to use to connect to a database.
  2. Specify the type of result set that the SQL statement returns.
  3. Specify a time-out for the SQL statements.
  4. Specify the source of the SQL statement.
  5. Indicate whether the task skips the prepare phase for the SQL statement.
  6. If we use the ADO connection type, we must indicate whether the SQL statement is a stored procedure. For other connection types, this property is read-only and its value is always false.
The Execute SQL Task of SSIS is extraordinarily useful. We can use parameter markers to dynamically provide parameter values. The rules for which parameter markers and parameter names can be used in the SQL statement depend on the type of connection manager that the Execute SQL uses.

The Execute SQL task can be used in combination with the Foreach Loop and For Loop containers to run multiple SQL statements. These containers implement repeating control flows in a package and they can run the Execute SQL task repeatedly.
For example, using the Foreach Loop container, a package can enumerate files in a folder and run an Execute SQL task repeatedly to execute the SQL statement stored in each file.

Use Variable in Execute SQL Task
We can use one or more variables in Execute SQL Task to pull the information from the database.  To pull the values from database, we can use the SQL Statement or stored procedure with the parameters. To know more, click at Use Variable in Execute SQL Task.

Object Types Variable in Execute SQL Task
The most interesting fact about SQL queries and stored procedures is that, they are frequently use input parameters, output parameters, and return codes. The Execute SQL task supports the Input, Output, and ReturnValue parameter types. To know more, click at SSIS - Object Types Variable in Execute SQL Task.

No comments:

Post a Comment

Popular Posts