Friday, 12 September 2014

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.