SQL Server Magazine

The first version of Data Transformation Services (DTS), which Microsoft introduced with SQL Server 7.0, gave database professionals an easy-to-use, low-cost alternative to more expensive products in the data extraction, transformation, and loading (ETL) market. The first versions of most products leave gaps in their coverage, however, and DTS was no exception. Microsoft provided several enhancements in SQL Server 2000 that significantly increase DTS's power and usability. Two new tasks, as well as upgrades to an existing task, are standout improvements. Let's walk through a data ETL scenario that showcases these features as you create a SQL Server data mart from the FoodMart sample database that ships with SQL Server 2000.

Introducing the Create FoodMart 2000 Package
How many times have you wished that you could put SQL Server through its paces on a database larger than Northwind and Pubs? Actually, SQL Server ships with the larger FoodMart sample database, which is the source database for the FoodMart Analysis Services cube. The FoodMart database has just one drawback—it's a Microsoft Access database. I created a set of DTS packages that takes the Access database and moves it to SQL Server. (You can download the files you need to run the sample DTS packages. For download instructions, see "More on the Web," page 54.)This scenario provides a good framework for introducing DTS's key new features.

Before diving into the details, let's look at Figure 1, which shows the Create Foodmart 2000 DTS package. You can break down this package into 15 tasks that you group into five main steps:

• initializing global variables and the package state (Tasks 1—2)
• deleting the FoodMart database if it exists (Tasks 3—6)
• creating the FoodMart database and tables (Tasks 7—10)
• moving data from Access to SQL Server (Task 11)
• cleansing the data, creating star indexes, and adding referential integrity (Tasks 12—15)

Before looking at these steps in detail, let's look at global variables—the glue that holds the package together.

Initializing Global Variables and the Package State
Global variables are the nerve center of a DTS package because they provide a central location for DTS to share information. To create, view, and set global variable values, go to the DTS Package Designer's toolbar, select Package Properties from the menu, then click the Global Variables tab, which Figure 2 shows. SQL Server 2000's enhanced task support for global variables incorporates multiple task types—including ActiveX Script, Dynamic Properties, and Execute SQL tasks—which can set and retrieve global variable values. DTS 2000 and DTS 7.0 also support a wide range of data types, including COM components. The ActiveFoodMartConnections global variable, which Figure 2 shows, is an example of a COM component. This global variable, which I created as an output parameter in Task 4, stores an ADO Recordset object that contains records describing all active FoodMart connections.

Task 1: Initializing global variables. To initialize the package global variables, you can write VBScript code into an ActiveX Script task, as Listing 1 shows. In VBScript, global variable assignments take the form

DTSGlobalVariables("name").Value = "Input-value"

where name is the global variable's name and Input-value is the value that you assign to the global variable. Note that although I use VBScript for all packages, you can also use any other installed ActiveX scripting language, such as JScript or Perl.

Task 2: Using .ini files to initialize global variables. Now, let's look at the way the new Dynamic Properties task removes one of DTS 7.0's major limitations—the inability to set key package, task, and connection values at runtime from outside the DTS environment. In DTS 7.0, developers had to manually configure packages as they moved through the package life cycle—from development to testing and finally to production. With DTS 2000, the package remains unchanged through the development life cycle; only the parameter settings made outside the package change. In this example, I use Windows .ini files to initialize the global variables. You can also initialize environment variables, database queries, DTS global variables, constants, and data files. Figure 3 shows the global variables that you can initialize. Note that the window also includes Connections, Tasks, and Steps properties. Later in this article, I show you how to initialize both Connections and Tasks properties. Each global variable is linked to one entry within the specified .ini file. Figure 4 shows the Add/Edit Assignment dialog box, in which you initialize the SQLServerName global variable with the SQLServerName key from the C:\Create-foodmart.ini file. Listing 2 shows the Createfoodmart.ini file code. Note that this .ini file is the only parameter in this package that isn't dynamic. You need to place it in the C directory or modify the task to point to the .ini file's new location.

The next two instances of the Dynamic Properties task use these initialized global variables to dynamically set important connection information, the SQL Server database files directory, and the CopyFoodMart DTS package filename, package name, and package GUID. The next four tasks delete active FoodMart database users and drop any existing FoodMart database to make sure that the system is ready for the database creation.

Preparing the Existing Environment
Task 3: Setting the connection parameters. The power of the Dynamic Properties task becomes evident when you set the connection parameters. The Dynamic Properties task uses the global variables that the .ini files have already initialized to initialize SQL Server OLE DB connection properties. DTS in turn uses the connection properties to connect to SQL Server. On the General tab in the Dynamic Properties Task Properties window, which Figure 5 shows, you can see that global variables set three connection parameters and a constant value sets one parameter. Clicking Edit brings you to the Dynamic Properties Task: Package Properties window, which Figure 6 shows. The window displays the specific property (in this case the OLE DB Data Source property) that the global variable is initializing. Clicking Set takes you back to the Add/Edit Assignment dialog box.

   Prev. page   [1] 2 3 4     next page