08/05/2008 01:28:33
This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
This sample works with the SQL Server 2005 version of the AdventureWorks OLTP database. To install this database, see
The AWDataWarehouseRefresh sample package illustrates how to create and populate user tables in the new AdvWorksDWX database, using data from the AdventureWorks database. Several Transact-SQL scripts are executed to create the tables, after which several Execute SQL tasks run to populate the tables.
| Important: |
|---|
| Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples. |
Requirements
Running this sample package requires the following:
-
The sample package and data files that it uses must be installed on the local hard disk drive.
-
You must have installed and have administrative permissions on the AdventureWorks OLTP database.
-
If you intend only to run the sample package from the command line, you must install Integration Services.
-
If you intend to open the package in SSIS Designer and run the sample package, you must install Business Intelligence Development Studio.
The following files are required to run this sample package.
| File | Description |
|---|---|
|
AWDWRefresh.dtsx |
The sample package. |
For more information about how to install samples, see "Installing Sample Integration Services Packages" in SQL Server Books Online.
Location of the Sample Package
If the samples were installed to the default location, the AWDataWarehouseRefresh sample package and data files are located in the following folder:
C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\AWDataWarehouseRefresh\
| Note: |
|---|
| If you changed the installation location of the project, you must modify the BasePath and DataFilePath variables in the package. |
Running the Sample
The package can be run from the command line by using the dtexec utility, or can be run in Business Intelligence Development Studio.
To run the package by using dtexec
-
Open a Command Prompt window.
-
Use the Change Directory command, cd, to change the directory to C:\Program Files\Microsoft SQL Server\100\DTS\Binn, the location of dtexec.
-
Type the following command:
dtexec /f "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\AWDataWarehouseRefresh\AWDataWarehouseRefresh\AWDWRefresh.dtsx"
-
Press Enter.
For more information about how to run the package by using the dtexec utility, see the topic, "dtexec Utility", in SQL Server Books Online.
To run the package in Business Intelligence Development Studio
-
Open Business Intelligence Development Studio.
-
On the File menu, point to Open, and then click Project/Solution.
-
Set the Files of type: to Integration Services Project Files (*.dtproj), locate the AWDataWarehouseRefresh folder, and then double-click the file named AWDataWarehouseRefresh.dtproj.
-
In Solution Explorer, right-click AWDWRefresh.dtsx in the SSIS Packages folder, and then click Execute Package.
Components in Sample
The following table lists the tasks, containers, data sources and destinations, and transformations that are used within the sample.
You can browse the objects in the package by selecting any object on the Control Flow tab and then pressing SHIFT+TAB to advance through the objects one by one. You can also see a big picture of the package by clicking the four-way locator arrow at the lower right corner of the Control Flow design surface, and then positioning the view window over the package.
Because this package represents an example of a production database, there are many objects and containers, collected into logical groups. You can collapse the groups to make it easier to view the whole package, or expand a group to better see the items in a particular group. The tasks and containers in the package are listed here by their groups.
Database and Tables Group
The group, Database and tables, contains a Sequence container, a Foreach Loop container, and an Execute SQL task. These containers in turn include the tasks listed in this table.
| Element | Purpose |
|---|---|
|
Sequence Container |
The Sequence container, Prepare New Database, contains three Execute SQL tasks that create the new database and set database options. The sequence is followed by a Foreach Loop container and another Execute SQL task. |
|
Execute SQL task |
The Execute SQL task, Drop database, deletes any existing instances of AdvWorksDWX. |
|
Execute SQL task |
The Execute SQL task, Create database, creates a new database, AdvWorksDWX. |
|
Execute SQL task |
The Execute SQL task, Set database options, executes an ALTER DATABASE statement. |
|
Foreach Loop container |
The Foreach Loop container, Create tables, contains an Execute SQL task that runs SQL statements from files that match the file filter CreateTable-*.sql. |
|
Execute SQL task |
Creates tables in the AdvWorksDWX database by running SQL statements from files specified in the Foreach Loop. |
|
Execute SQL task |
The Execute SQL task, Create udfMinimumDate function, creates a user-defined function by using direct input of an SQL statement. |
Temporary Tables Group
The group, Temporary Tables, contains two Foreach Loops, each containing an Execute SQL Task that loops through SQL statements in files. The location of the folder that contains the files is defined by a file connection manager.
| Element | Purpose |
|---|---|
|
Foreach Loop container |
The Foreach Loop, Drop temp tables, contains an Execute SQL task that runs SQL statements from files that match the file filter DropTempTable-*.sql. |
|
Execute SQL task |
Drops temporary tables in the AdventureWorks database by running SQL statements from files specified in the Foreach Loop |
|
Foreach Loop container |
The Foreach Loop, Create temp tables, contains an Execute SQL task that runs SQL statements from files that match the file filter CreateTempTable-*.sql. |
|
Execute SQL task |
Creates temporary tables in the AdventureWorks database by running SQL statements from files specified in the Foreach Loop. |
Load Temporary Tables Group
The group, Load temporary tables, contains seven Bulk Insert tasks that load data from .csv files into temporary tables in the AdventureWorks database.
| Element | Purpose |
|---|---|
|
Bulk Insert task |
The Bulk Insert task, Bulk Insert tempProductSubcategory-ForeignNames, inserts data into a temporary table. |
|
Bulk Insert task |
The Bulk Insert task, Bulk Insert tempProduct-ForeignNames, inserts data into a temporary table. |
|
Bulk Insert task |
The Bulk Insert task, Bulk Insert tempStore-MinPayment, inserts data into a temporary table. |
|
Bulk Insert task |
The Bulk Insert task, Bulk Insert tempProductCategory-ForeignNames, inserts data into a temporary table. |
|
Bulk Insert task |
The Bulk Insert task, Bulk Insert tempCustomer-YearlyIncome, inserts data into a temporary table. |
|
Bulk Insert task |
The Bulk Insert task, Bulk Insert tempIndividual-ForeignData, inserts data into a temporary table. |
|
Bulk Insert task |
The Bulk Insert task, Bulk Insert tempSpecialOffer-ForeignData, inserts data into a temporary table. |
Create Keys and Indexes Group
The group, Create Keys and Indexes, contains four Execute SQL tasks connected by precedence constraints, and also contains a Foreach Loop container.
| Element | Purpose |
|---|---|
|
Execute SQL task |
The Execute SQL task, Add Primary Keys, creates primary keys on the new tables in AdvWorksDWX. |
|
Execute SQL task |
The Execute SQL task, Add Indexes, indexes the new tables in AdvWorksDWX. |
|
Execute SQL task |
The Execute SQL task, Add Foreign Key Constraints, adds constraints to the new tables in AdvWorksDWX. |
|
Execute SQL task |
The Execute SQL task, Drop udfMinimumDate function, deletes the user-defined function from AdvWorksDWX. |
|
Foreach Loop container |
The Foreach Loop container, Drop temp tables 1, contains an Execute SQL task that runs SQL statements from files that match the file filter DropTempTable-*.sql. |
|
Execute SQL task |
Deletes temporary tables from the AdventureWorks database by running SQL statements from files specified in the Foreach Loop. |
Ungrouped Tasks
The package also contains 25 tasks that are not in any group, but perform operations that move data between tasks in the other groups.
| Element | Purpose |
|---|---|
|
Bulk Insert task |
The Bulk Insert task, Bulk Insert FactFinance, loads data from a .csv file into a fact table in AdvWorksDWX. |
|
Bulk Insert task |
The Bulk Insert task, Bulk Insert DimAccount, loads data from a .csv file into a dimension table in AdvWorksDWX. |
|
Bulk Insert task |
The Bulk Insert task, Bulk Insert AdventureWorksDWBuildVersion, updates the version information for the data warehouse in AdvWorksDWX. |
|
Data Flow task |
The Data Flow task, Data Flow Task - DimPromotion, contains an OLE DB source and a SQL Server destination. The data flow runs a query against AdventureWorks to identify promotions and writes the results to a dimension table in AdvWorkDWX. |
|
Bulk Insert task |
The Bulk Insert task, Bulk Insert DimOrganization, loads data from a .csv file into a dimension table in AdvWorksDWX. |
|
Data Flow task |
The Data Flow task, Data Flow Task - DimSalesReason, contains an OLE DB source and a SQL Server destination. The data flow runs a query against AdventureWorks to look up sales reasons and writes the results to a dimension table in AdvWorkDWX. |
|
Data Flow task |
The Data Flow task, Data Flow Task - DimReseller, contains an OLE DB source, a Data Conversion transformation, and a SQL Server destination. The data flow performs a complex query, including conditional statements and lookups, against AdventureWorks and writes the results to a dimension table in AdvWorksDWX. |
|
Data Flow task |
The Data Flow task, Data Flow Task - DimEmployee, contains an OLE DB source and a SQL Server destination. The data flow performs lookups on a Human Resources table, assigns salesperson IDs, and writes the results to a dimension table in AdvWorksDWX. |
|
Data Flow task |
The Data Flow task, Data Flow Task - DimCurrency, contains an OLE DB source and a SQL Server destination. The data flow extracts data from a currency table, changes the column names, and writes the data to a dimension table in AdvWorksDWX. |
|
Bulk Insert task |
The Bulk Insert task, Bulk Insert DimScenario, loads data from a .csv file into a dimension table in AdvWorksDWX. |
|
Bulk Insert task |
The Bulk Insert task, Bulk Insert - DimTime, loads data from a .csv file into a dimension table in AdvWorksDWX. |
|
Data Flow task |
The Data Flow task, Data Flow Task - FactSalesQuota, contains an OLE DB source and a SQL Server destination. The data flow extracts quotas for salespeople and updates a fact table in AdvWorksDXW. |
|
Data Flow task |
The Data Flow task, Data Flow Task - FactCurrencyRate, contains an OLE DB source and a SQL Server destination. The data flow extracts and converts currency information by using SQL statements, and then updates a fact table in AdvWorksDXW. |
|
Data Flow task |
The Data Flow task, Data Flow Task - DimProduct, contains an OLE DB source and a SQL Server destination. The data flow performs complex processing of data in temporary tables by using SQL statements, and then updates a dimension table in ADvWorksDWX. |
|
Data Flow task |
The Data Flow task, Data Flow Task - DimProductCategory, contains an OLE DB source and a SQL Server destination. The data flow maps foreign product categories to English category names and updates a dimension table in ADvWorksDWX. |
|
Data Flow task |
The Data Flow task, Data Flow Task - DimProductsSubcategory, contains an OLE DB source and a SQL Server destination. The data flow extracts translations for product subcategories from a reference table and then updates a dimension table in ADvWorksDWX. |
|
Data Flow task |
The Data Flow task, Data Flow Task - DimGeography, contains an OLE DB source and a SQL Server destination. The data flow looks up addresses in a reference table and then writes the results to a dimension table in AdvWorksDWX. |
|
Data Flow task |
The Data Flow task, Data Flow Task - DimCustomer, contains an OLE DB source and a SQL Server destination. The data flow takes multiple inputs—the data from DimGeography, and two temporary tables—and extracts data for updating a dimension table in AdvWorksDWX. |
|
Data Flow task |
The Data Flow task, Data Flow Task - FactInternetSalesReason, contains an OLE DB source and a SQL Server destination. The data flow extracts data from AdventureWorks and updates a fact table in AdvWorksDWX. |
|
Data Flow task |
The Data Flow task, Data Flow Task - DimSalesTerritory 1, contains an OLE DB source and a SQL Server destination. The data flow looks up regional codes for territories and writes the results to a dimension table in AdvWorksDWX. |
|
Data Flow task |
The Data Flow task, Data Flow Task - DimSalesTerritory 2, contains an OLE DB source and a SQL Server destination. The data flow adds country/region mappings to the data processed in the previous data flow. |
|
Data Flow task |
The Data Flow task, Data Flow Task - DimDepartmentGroup, contains an OLE DB source and a SQL Server destination. The data flow changes all group names to Corporate and writes the values to a dimension table in AdvWorksDWX. |
|
Data Flow task |
The Data Flow task, Data Flow Task - DimDepartmentGroup 1, contains an OLE DB source and a SQL Server destination. The data flow looks up group names in a human resources table and updates the values in the dimension table in AdvWorksDWX. |
|
Data Flow task |
The Data Flow task, Data Flow Task - FactInternetSales, contains an OLE DB source and a SQL Server destination. The data flow takes multiple inputs from other tasks, including the newly created user-defined function, and updates a fact table in AdvWorksDWX. |
|
Data Flow task |
The Data Flow task, Data Flow Task - FactResellerSales, contains an OLE DB source and a SQL Server destination. The data flow takes multiple inputs from other tasks, including the newly created user-defined function, and updates a fact table in AdvWorksDWX. |