Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services

Chapter 10. Using the Execute SQL Task

When you are creating a SQL Server Integration Services (SSIS) package, you will find 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 on 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 10-1, you see the main properties that need to be 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 10-1. Figure 10-1

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