O'Reilly logo

Professional SQL Server™ 2005 Integration Services by Mike Murphy, Haidong Ji, Jason Gerard, Erik Veerman, Andy Leonard, Kathi Kellenberger, Douglas Hinson, Darren Green, Allan Mitchell, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required