Database Reference
In-Depth Information
Figure 7-12.
Using data transformation tasks
CONSIDer StaGING taBLeS
if your data source is a file, you may find it advantageous to load the data from the file into a temporary
staging table in the database. You can perform ETL transformations using sQL code from there.
This additional step may seem like extra work, but if there are enough transformations involved, you will
find the process faster than performing transformations directly through ssis. This is because the database
engine can process transformations on large datasets quicker than ssis can. while ssis is fast, especially
compared to its predecessors, it is still not as fast as a dedicated relational database engine for processing
data.
Another advantage to this technique is that it is easier to find team members who understand sQL
programming statements than it is to find ssis experts. Using a combination of sQL statements and data
flows provide a fast and easy way to include ETL programming code within a Visual studio solution.
Data Destinations
Data flow destination tasks allow you to place data in files, in tables, or even in an in-memory dataset. There
are quite a few data flow destinations that come with SSIS at first install, and you can download others from the
Microsoft website. The installed items are listed at the bottom of the SSIS Toolbox (Figure
7-13
). If they are not
enough to meet your needs, you can create custom destinations.
A data flow destination must have an input from another data flow component, and it must be connected
before you start editing it. This is important because the input provides the metadata describing the list of source
columns that are mapped to columns in the destination. Without this metadata, the data flow destination cannot
be configured correctly.
