1157
Chapter 52: Building, Deploying, and Managing ETL Workfl ows in Integration Services
52
There are some other enhancements which don’t fi t neatly into the above three categories. ODBC
sources and destinations have been added to the data fl ow to prepare for Microsoft’s planned
deprecation of OLE DB. Another recent addition is that Visual Studio confi gurations can now be
used with SSIS.With the enhancements of SSIS in SQL Server 2012, the changes do not change the core data fl ow
design of SSIS. You apply business logic to ETLs in the same ways as with previous versions; the
improvements remove unnecessary impediments, namely the steep learning curve of SSIS and
Package Confi gurations.Exploring the SSIS Environment in Brief
In order to get a high level view of Integration Services you are going to step through an
example. This example will use the AdventureWorks database, available for free download
at http://www.codeplex.com.Imagine that you are a database developer for Adventureworks Bicycles. Business has been
fantastic, and the company expanded its sales to an increasing number of outlets. Many
websites now carry your bicycles so that you receive Product Reviews from multiple sources,
which you must import via text fi les.You are tasked with creating the SSIS Project that can load these reviews into the
AdventureWorks database, specifi cally the Production.ProductReview table, as shown in
Figure 52-2. You start with one Product Review site: BikesNSuch. The fi les you receive
are named bikesnsuch.txt/. See Figure 52-1 for the fi le layout. The fi le contains the
ProductNumber, which you must use to locate and load the actual ProductID in
the Adventureworks database. The sample fi le can be downloaded from this book’s
website.FIGURE 52-1
The source fi le. Notice the absence of a ProductID.You extract the source fi le’s rows, transform the data to the proper specifi cations and for-
mat, and load it into the Adventureworks database. The transforms include fi nding the
ProductID by ProductNumber and adding the Modifi ed Date.c52.indd 1157c52.indd 1157 7/31/2012 10:29:24 AM7/31/2012 10:29:24 AM
http://www.it-ebooks.info