- 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.