Professional SQL Server™ 2005 Integration Services

6.1. Execute SQL Task

The Execute SQL task is probably not one of the more advanced SSIS tasks, but it is one of the most used task in SQL Server 2000 DTS. You'll use it as a jumping-off point to cover variables, expressions, and some advanced techniques of runtime property management.

The Execute SQL task is a task often used to truncate staging data table prior to import. Another common use is to call stored procedures to take on the task of performing complicated business logic that couldn't be modeled in the DTS package environment. The SSIS version can provide these same services, but now with a better configuration editor and methods to map stored procedure parameters and to read back result and output values.

Create an SSIS package and drag an Execute SQL task on the Control Flow design surface. The control will show a red icon on the task, and an error message appears in the error list to inform you that there is no connection associated with this task. You'll have to open up the Execute SQL task editor and set that up to satisfy that error condition. Double-click the task, and your editor should look similar to Figure 6-1.

Figure 6.1. Figure 6-1

The task has four sections in the Execute SQL task editor:

• General: Contains the properties to allow the setting of the task name, description, connection-related settings, setting to indicate how to execute the SQL statement, ...