SQL Server Source Patterns - SQL Server Integration Services Design Patterns - page 136

variable that contains that information. This option is not recommen-
ded.
SQL Command: Similar to access in the ADO.NET source, you can
enter the SQL query or execution of a stored procedure once you've
selected this option.
SQL Command from Variable: If you want to create a query to
change at runtime or to pass a variable to a stored procedure, this is
the option you will want to use. Instead of creating an expression to
modify the SQL Command, as you did with ADO.NET, you will cre-
ate a variable that creates your expression. You can then select the
variable you created after you select this option.
By picking one of these options, you will determine how the data is returned from
SQL Server. After you select the type of data retrieval, you'll want to add the appropri-
ate properties. For example, if you select the Table or View option, you'll need to select
the object that contains the data. If you select the SQL Command option, you'll need to
enter the SQL query or stored procedure execution that returns the data. Once that is
set, you can move on to designing the rest of your data flow.
Waste Not, Want Not
As data professionals, we often think that the more data we can get, the better. This
isn't always the best scenario when you are dealing with sources. When you are talking
about the amount of data to pull, you will want to follow a different pattern.
No matter which query option you selected, it is important to only ask for the
columns that you need in your data load process. Requesting all columns is similar to
running a select * from table query against a database. Not only are you asking
the database and network to do more work, but you are also asking Integration Services
to do more work. All of that unnecessary data will get stored in memory, or even cause
paging if there isn't enough memory, using up space that could be used to grab more
data for the important columns and slowing down the overall package execution.
All source components give you the option to pick a subset of columns on the
Columns menu. Be sure to make the column reduction in the query itself rather than in
the Columns menu to reap the full benefit of a faster package.
Data Translations