Database Reference
In-Depth Information
SQL Command and SQL Command from a Variable
The “SQL command” choice allows you to type or copy and paste a SQL statement into the command window
(Figure
8-8
). As you might have already concluded, the SQL command from a variable does the same thing, as
long as you place your SQL code into an SSIS variable first. You can use either of these data access modes to
execute stored procedures as well.
Figure 8-8.
Using the SQL command data access mode
■
The recommended practice is to use sQl server Management studio to create and test your sQl code. The
steps involved include writing the select statement, wrapping the statement into a sQl stored procedure, testing
that it works as expected, and then using the stored procedure's name in the sQl command text window. The stored
procedure would include most, if not all, the ETl transformations required and provides your source component with
clean transformed data. All the actual ETl transformation processing happens within the database engine as the
stored procedure is executed. This is both faster and less prone to errors. You may remember that in Chapter 6 we
created all the select statements we needed, but to keep things simple, we did not put them into stored procedures.
in a production environment, you should consider using stored procedures instead.
Tip
