Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Chapter 12

Using the Execute SQL Task

When you are creating a SQL Server Integration Services (SSIS) package, you will find that one of the most commonly used tasks is the Execute SQL Task. This task is used to insert, update, select, and truncate data from SQL tables. Any normal SQL commands you would use can be used in this task. You can use parameters just like a stored procedure and can even call stored procedures from the task. A connection to the database must exist in the connection manager for the Execute SQL Task to reference.

Double-click an Execute SQL Task in the Control Flow to open the Execute SQL Task Editor. The first screen on the editor lists four nodes in the left pane:

• General
• Parameter Mapping
• Result Set
• Expressions

In the General node, shown in Figure 12-1, you see the main properties that you need to set for the Execute SQL Task. The first two properties are Name and Description. These properties do not affect the task. They are used for ease of reference when viewing the task in the Control Flow. The name shows on the task in the Control Flow. The description is usually a longer line of text describing the purpose of the Execute SQL Task. It is a best practice to always change the values of these fields to values that make it easy for anyone to see and understand the function of the task.

Figure 12-1

The next two options are the TimeOut and CodePage. ...