SSIS Execute SQL Task is used to execute sql code on source connection. Below are the stetp by step guide to create a SQL node in SSIS:
- Open SSIS Project.
- In Control Flow window drag and drop Execute SQL task from toolbox.
- Double click Execute SQL Task to edit.
- In Execute SQL Task Editor provide an connection (Click here to know how to create a new connection in SSIS).
- In SQLStatement field provide appropriate SQL code.
- Do not change other default settings. Click on OK button to save the changes.
Click play to execute your first SSIS package with Execute SQL task.
SSIS Execute SQL Task Editor options:
- General Tab
- Name: This is the display name of Execute SQL Node.
- Description: It is used to tell other what is the purpose of this node.
- TimeOut: It will tell SSIS about the time out value. 0 Means infinity.
- Result Set:
- Result Set: It tells SSIS about result set. We have a separate article here to explain this.
- SQL Statment:
- Connection Type: Need to select the type of connection (Excel, OLED DB, ODBC, ADO, ADO .NET, SQLMOBILE)
- Connection: Need to select connections (If not exists create a new one, here is the tutorial).
- SQLSourceType: It will decide from where the SQL Statement is stored. Valid Values (Direct Input, File Connection, Variable here is the tutorial for different values) .
- IsQueryStoredProcedure: Valid Values (True | False)
- ByPassPrepare: This option decide whether to create SQL Statement Dynamically or not. (Here is the tutorial for the same).
- Parameter Mapping: It is used to map the SSIS Variables and Parameters used in SQL Statement. Here is a separate article on this.
- Result Set: It is used to map result of the query with external SSIS Variables. Here is a separate article on this.
- Expressions: It is used to dynamically set the properties of Execute SQL Task.
Your valuable comments will be highly appreciated.