Adventureworks2008 Sr4 Exe
By: Ray Barley Last Updated: 2008-06-03 Comments (7) Related Tips: More >Integration Services Data Flow Transformations
Problem
One of the new SQL Server 2008 features in SSIS is an enhanced Lookup transform that includes the ability to cache the lookup data locally. Can you provide the details of this new functionality and an example of how to use it?
Which of the AdveWks DB do I download: AdventureWorks2008_SR4.exe OR SQL2008.AdventureWorksLT2008_Only_Database.zip Both of two setups are for SQL Server 2008. If you have an instance of SQL Server 2008, I would suggest installing the first setup which will install all AdventureWorks sample databases. I have uninstall Adventureworks2008 SR4 DB and then install it again. When the installation completed without any error, I opened SSMS and found all the databases attached except Adventureworks2008 DB. SO, I opened up the script file located at (C: Program files Sql Server 100 tools samples Adventureworks2008 OLTP instawdb). Then I followed all.
Solution
The enhanced Lookup transform works hand-in-hand with the Cache transform. The Cache transform is a brand new feature in SQL 2008, allowing you to cache the data used in the Lookup transform. The Lookup transform can then utilize this cached data to perform the lookup operation. Working with cached data will most likely be more efficient than querying the database. In addition the Cache Connection Manager (another new feature) can persist the cache to a local file, allowing the cache to be shared between multiple SSIS packages and steps within a package. A perfect example of where this will be useful is in the extract, transform and load (ETL) packages that we create to update a data warehouse. We typically build dimension tables in the data warehouse that use a surrogate key as a primary key (in lieu of the source key in the business application). As we are processing our fact tables we need to lookup the dimension surrogate keys based on the key in our source system and store the surrogate key in our fact tables.
To demonstrate how to use the new Lookup and Cache transforms, we will create a simple SSIS package as shown below:
I've been combing Codeplex downloads, trying to install Adventureworks2008 for my 2008 version of SQL Server, and the site is stymying my efforts. Several times now, I've downloaded installed AdventureWorks2008_SR1.exe and AdventureWorks2008_SR4.exe, and AdventureWorks2008 never shows up.
The Build Customer Cache Data Flow task will use the new Cache Transform component to populate the cache with the source key and surrogate key for every row in the Customer dimension from the AdventureWorksDW 2008 database. Note that we could create a separate SSIS package to populate our cache then simply use the cache in multiple SSIS packages. We could run the SSIS package only when there has been a change to the Customer dimension. For purposes of demonstration, we will just use a single package.
The Lookup Customer Surrogate Keys Data Flow task will use the enhanced Lookup transform to lookup the Customer dimension surrogate key based on the source key in the Sales.Customer table of the AdventureWorks2008 OLTP database.
Initial Setup
Before we walk through the details of the two Data Flow tasks, let's first setup the Cache Connection Manager. The Cache Connection Manager is a new connection manager which is used to define the cache. To add a Cache Connection Manager right click in the Connection Managers area of the Control Flow design surface and select New Connection. Select CACHE from the Add SSIS Connection Manager dialog then click the Add button:
After adding the Cache Connection Manager there are some properties that need to be configured. On the General tab click the Use file cache checkbox and enter a filename for the cache; this will persist the cache to a local file, allowing it to be reused on a subsequent run of this package or another package.
Click the Columns tab to configure the data columns to be stored in the cache. SourceKey is the name we are using for the lookup column; i.e. it is the column that is joined to the source table to perform the lookup; the Index Position must be 1. The SurrogateKey column is the value that we want to retrieve via the lookup; i.e. it's the value that we want to retrieve based on our SourceKey; its Index Position must be zero. The Columns dialog below assumes a single column lookup; if more than one column is required for the lookup, add the additional columns and increment the Index Position as appropriate.
Build Customer Cache
The Build Customer Cache Data Flow is shown below:
Get Customer Source Keys and Surrogate Keys is an OLE DB Source that selects the CustomerAlternateKey (i.e. the SourceKey) and the CustomerKey (i.e. the surrogate key) for every row in the Customer dimension. The SELECT statement aliases the columns to SourceKey and SurrogateKey for convenience; they will match the column names in the cache and the Mappings dialog in the Cache transform (shown below) will automatically perform the mapping.
Load Customer Lookup Cache is a Cache Transform component that loads the cache. The Cache Transform component requires a Cache Connection Manager which defines the cache. We defined the Cache Connection Manager earlier in the Setup section. Select the Cache connection manager on the Connection Manager page of the Cache Transformation Editor:
Click Mappings to map the input columns in the Data Flow to the destination columns in the cache. Based on the aliases in our SELECT statement above, the mapping is performed automatically.
Lookup Customer Surrogate Keys
The Lookup Customer Surrogate Keys Data Flow task is shown below:
Adventureworks2008_sr4.exe Free Download
Get Orders is an OLE DB Source that retrieves the SourceKey by joining the Sales.SalesOrderHeader table to the Sales.Customer table. The CAST is required to match the type in the cache and the Customer dimension. The SourceKey is what we join to the cache to perform our lookup.
Lookup Customer Surrogate Keys is a Lookup transform. It allows us to retrieve the SurrogateKey for the SourceKey in our Data Flow. There are a number of properties to configure starting with the General page:
For Cache mode we select Full cache which preloads the cache before performing the lookups. For Connection type we select Cache connection manager (a new feature) which allows us to preload the cache from a file that exists locally (we created it in the initial Data Flow task). These two options maximize performance of the Lookup transform in most instances. You can still choose an OLE DB connection manager which is essentially the functionality you get with SQL Server 2005. Another new feature in SQL Server 2008 is the ability to redirect rows with no match to a new output (Lookup No Match Output) rather than redirecting these rows to the Error output. In most cases a lookup with no match will need to be handled separately but it may not be treated as an error.
Click Connection to specify the Cache Connection Manager; we defined this in the Setup section. It links the Lookup transform to the underlying cache.
Click Columns to configure the lookup operation; the completed dialog is shown below:
Start out by right clicking on the lookup column in the Available Input Columns list; for our example right click the AccountNumber column. The Create Relationships dialog will be displayed; select AccountNumber as the Input Column and SourceKey as the Lookup Column.
Click SurrogateKey in the Available Lookup Columns list; this is the value that we want to retrieve from the lookup operation.
Save Customer Lookup Matches and Save Customer No Match Lookups are both OLE DB Destinations; we save the successful lookups and any source keys with no match in the cache to separate tables just to see the result of the Lookup transform. In a typical SSIS package you would assign new surrogate keys for the No Match Lookups then insert all of the rows extracted into the appropriate fact table. The No Match lookups represent what we call early arriving facts; i.e. a row from the source system that references a dimension source key that doesn't exist yet. For additional information on handling these early arriving facts, refer to our earlier tip Handling Early Arriving Facts in SQL Server Integration Services (SSIS) Packages.
Summary
Let's highlight the key points in our sample SSIS package that uses the enhanced Lookup and new Cache transforms:
- Use the new Cache Connection Manager to define your cache.
- Load the cache using the new Cache transform.
- Configure the enhanced Lookup transform to use the new Cache Connection Manager.
Next Steps
- Download a copy of the latest Community Technology Preview of SQL Server 2008 from this site. The above example was created using the February, 2008 CTP.
- Download the sample code and experiment with the new Lookup transform in an SSIS package. Note that you will probably have to change the cache filename in the Cache Connection Manager; the example uses a hard-coded path. You will also have to create the tables used in the Lookup Customer Surrogate Keys Data Flow task.
- Download the AdventureWorks sample databases for SQL Server 2008 here.
Last Updated: 2008-06-03
About the author
View all my tips
I have searched lot about AdventureWorks2008R2 database, but couldn't get proper URL.
Can anybody please tell the location (URL) where can I download from?
closed as off-topic by ayaio, Cody Gray♦Feb 16 at 10:21
This question appears to be off-topic. The users who voted to close gave this specific reason:
- 'Questions asking us to recommend or find a book, tool, software library, tutorial or other off-site resource are off-topic for Stack Overflow as they tend to attract opinionated answers and spam. Instead, describe the problem and what has been done so far to solve it.' – ayaio, Cody Gray
2 Answers
EDIT
As you can see in the comments of this download page, maybe there are some problem with the download process. I also tried it, and the first time it didn't run. The file may seem to be complete but it is not. Try to download one more time until you get the exact size of 86302720 bytes. (Nov 17 2010, Stable version)
The above link now lands at a redirect page.
The updated location is now on GitHub at:https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks