Using Queries in Packages

The Execute SQL task, the OLE DB source, and the Lookup transformation can use queries. In the Execute SQL task, the SQL statements can create, update, and delete database objects and data; run stored procedures; and perform SELECT statements. In the OLE DB source and the Lookup transformation, the SQL statements are typically SELECT statements or EXEC statements. The latter most frequently run stored procedures that return result sets.

The SQL statement can be defined either by entering it directly in the designer, or by specifying a file connection or a variable that contains the statement. If you want to use a direct input, SSIS Designer provides Query Builder, a graphical tool for creating SQL queries.

A query can be parsed to establish whether it is valid. When parsing a query that uses a connection to SQL Server, the query is parsed, executed, and the execution outcome (success or failure) is assigned to the parsing outcome. If the query uses a connection to a data other than SQL Server, the statement is parsed only.

Many SQL statements use parameters. In SQL Server, you use variables to provide values to input parameters and to capture values from output parameters and return codes. The variables can be user-defined variables or system variables. However, system variables are read-only and they can be used only to provide values for input parameters.

Depending on the connection manager type and the provider that the connection uses, different parameter markers and parameter names must be used when you create queries. For more information about the parameter markers and parameter names to use when building the query, click one of the following topics:

Execute SQL Task

OLE DB Source

Lookup Transformation

Direct Input SQL

Query Builder is available in the user interface for the Execute SQL task, the OLE DB source, the OLE DB destination, and the Lookup transformation. Query Builder offers the following advantages:

• Work visually or with SQL commands.
  Query Builder includes graphical panes that compose your query visually and a text pane that displays the SQL text of your query. You can work in either the graphical or text panes. Query Builder synchronizes the views so that the query text and graphical representation always match.
• Join related tables.
  If you add more than one table to your query, Query Builder automatically determines how the tables are related and constructs the appropriate join command.
• Query or update databases.
  You can use Query Builder to return data using Transact-SQL SELECT statements, or to create queries that update, add, or delete records in a database.
• View and edit results immediately.
  You can execute your query and work with a recordset in a grid that lets you scroll through and edit records in the database.

Although Query Builder is visually limited to creating SELECT queries, you can type the SQL for other types of statements such as DELETE and UPDATE statements in the text pane. The graphical pane is automatically updated to reflect the SQL statement that you typed.

You can also provide direct input by typing the query in the task or data flow component dialog box or the Properties window.

SQL in Variables

If the source of the SQL statement in the Execute SQL task is a variable, you provide the name of the variable that contains the query. The Value property of the variable contains the query text. You set the ValueType property of the variable to a string data type and then type or copy the SQL statement into the Value property. For more information, see Integration Services Variables and Using Variables in Packages.

SQL in Files

The SQL statement for the Execute SQL task can also reside in a separate file. For example, you can write queries using tools such as the Query Editor in SQL Server Management Studio, save the query to a file, and then read the query from the file when running a package. The file can contain only the SQL statements to run and comments. To use a SQL statement stored in a file, you must provide a file connection that specifies the file name and location. For more information, see File Connection Manager.

See Also

Concepts

Integration Services Overview

Other Resources

Creating Packages in SSIS Designer

Help and Information

Getting SQL Server 2005 Assistance

Change History