Database Reference
In-Depth Information
Data Sources
Data sources collect data from a variety of locations but usually pull data from a database table by executing a
SQL select statement. These select statements are either generated for you by SSIS or manually added to the data
flow source editor, as shown in Figure
7-11
. For example, setting the data access mode (shown in Figure
7-11
)
to Table or View and choosing a table or view by name automatically creates a SQL statement for you. This is a
simple way to configure a data source but is usually not the most efficient. Writing your own SQL code, as shown
in the Figure
7-11
, is a much better practice.
Figure 7-11.
Using SQL code in a data source
Data Transformations
Data transformations are a group of tasks that perform transformations such as concatenations, lookups, data
conversions, sorting, and column aliasing, all of which can be performed using SQL code as well. We recommend
using a combination of both SQL programming and SSIS tasks to perform ETL transformations. To do this, place
SQL programming code that includes these transformations inside your data source tasks (Figure
7-11
). All of the
transformations are performed when your SQL query is executed. You are then able to send the transformed data
directly to the data destinations.
This process may not always work. If, for example, your data source is pulling directly from a flat file, such as
a comma-delimited data file (also known as a .csv file), you are not able to perform SQL programming statements
directly on the file. Instead, you can use the SSIS data flow transformation tasks to accomplish the same thing
(Figure
7-12
). This is helpful when your business needs require that you pull data directly from a file.
