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

Another Integration Services source trap that is easy to fall into is to perform the ma-
jority of the data transformations in the source query itself. Because Integration Ser-
vices developers often have a SQL background, we tend to want to use a familiar tool
to accomplish our tasks.
The types of data transformations that can be undertaken in either the source query
or the rest of the data flow include merging of datasets, case statements, string concat-
enation, and more. Remember that SQL Server is very good at set-based actions,
whereas Integration Services is very good at computationally expensive tasks that use a
lot of memory. Although you should test your individual situation, these are good rules
of thumb to follow.
Follow the pattern listed in Table 4-1 when you are deciding where to put your data
translation logic.

Table 4-1 . Data Translation Locations

Data Translation Concern Location

Merge datasets Set-based Source component

Case statements Memory intensive Data flow

String concatenation Procedural Data flow

Sorting data Set-based Source component

Source Assistant

Now that you have retrieved data from SQL Server the hard way, you're going to learn
the easy way to do the same thing. The Source Assistant is a new wizard introduced in
SQL Server 2012 that takes you through the steps of setting up your Source objects
without having to make many of the same decisions that you just had to go through.
This is a great way for people who are just getting started with Integration Services to
get up and running quickly.
To begin, create a new Data Flow task. As seen in Figure 4-10 , the Source Assist-
ant appears in the SSIS Toolbox. Initially it will be in the Favorites grouping, unless
you have moved the items around.