Execute SQL Task

Introduction:

• Execute SQL task is a very common task used inside SSIS packages.
• As the name says,it can execute a SQL query inside the control flow.

How to configure:

• Execute SQL task is present inside the toolbox in the control flow tab of a package.
• Drag the task and place it inside the control flow

• Double click on the task.

• The Execute SQL task editor will appear where you can see all different configurable properties of the task.
• Both DML and DDL SQL scripts can be executed by using the Execute SQL task.
• In this blog we will see how to execute a SQL script which doesn't return any result set.

Executing a SQL Statement which doesn't return any result set:

In this demo a create statement will be executed by Execute SQL task which will not return any result set.

• Let's say there is a DB named test.
• We are going to create a table named EMP with the following attributes.

• To execute the above statement inside the Execute SQL task we need to create a connection to the TEST Database. Please refer the following post to get the details regarding creating a connection:Creating connection manager in ssis
• Once the connection manager is created for the Database where the table need to be created go the Execute SQL Task editor.
• Chose Connection manager name from the Connection drop down
• Then place the SQL Statement inside the SQL Statement textbox.
• After that click OK and execute the package
• Once the package executed successfully you can see a EMP table is created inside the server mentioned in the OLEDB connection manager.
• If you try to re-execute the package it will throw error as the table is already created.