Task 4: Getting the FoodMart connection. After you set the connection parameters, you need to drop the existing FoodMart database. If users are logged in to the database, you have to terminate their sessions before you take that action. Figure 7 shows the General tab in the Execute SQL Task Properties window, which resembles the same tab in DTS 7.0. However, the Execute SQL Task Properties window in DTS 2000 incorporates the new Parameters button and the new "?" parameter marker in the SQL query. Clicking the Parameters button takes you to the Input Parameters tab in the Parameter Mapping window, which Figure 8 shows. This window lets you pass input parameters into the Execute SQL task and place output parameters from the Execute SQL task in global variables—actions you can't take in SQL Server 7.0. Let's take a closer look.
In the Parameter Mapping window, any global variable can set the SQL parameter marker, named Parameter 1. For this task, you pass the input FoodMart database name into the query by using the DatabaseName global variable. DTS 2000 packages give you the flexibility to specify the database name at runtime. In contrast, SQL Server 7.0 requires you to use additional SQL statements within the task to accomplish the same goal. Figure 9 shows how you cache the query's output recordset for use in the next task. On the Output Parameters tab, you can store one value at a time by first choosing the Row Value option, then mapping the SELECT LIST values one-to-one with global variables. You can use all values or a subset.
The ability to pass input parameters into the SQL task and place output parameters from the SQL task in global variables, as well as to store one value at a time, might seem minor at first. However, these features let you use the Execute SQL task in more places, providing a high-performance alternative to the DTS data pump transformation capability. As a general rule, set-based operations perform better than transformations. When I assembled DTS packages in SQL Server 7.0, I had to include additional SQL code within each task to set the correct input parameters and use temporary tables to store output parameters. In DTS 2000, you can eliminate from each SQL task the code you had to write in DTS 7.0 for passing input parameters and storing output parameters. In eliminating the code, you reduce the volume and complexity of code and therefore the time required to develop and test your DTS packages.
Task 5: Killing the FoodMart connections. To terminate processes that are accessing the FoodMart database, apply the SQL Server KILL command. Task 5's ActiveX script code loops through the rowset that is stored in the ActiveFoodMartConnections global variable, calling the code that Listing 3 shows. First, the ActiveX script builds the database connection string from DTS global variables, then saves the connection as a DTS global variable that future ActiveX Scripting tasks can use without first having to define it. You can use this connection to build and execute one KILL command for every server process ID (SPID) in the output rowset. After you kill all connections, you're ready to drop the existing FoodMart database.
Task 6: Dropping FoodMart. The ActiveX script that you run for Task 6 retrieves the ADO connection that you cached in the previous task, as Listing 4 shows. Then, you build the DROP DATABASE statement and execute it. Note that you have to build the statement explicitly each time for both the KILL and DROP DATABASE commands because the SQL Data Definition Language (DDL) doesn't support the "?" parameter marker. For that reason, you can't pass the database or SPID as an input parameter at the same time you pass the FoodMart database name. Now that you've finished cleaning up the environment, you're ready to build the new FoodMart database. Note that you designate the workflow from Task 6 to Task 7 as On Completion not On Success. You want the package to continue executing if the DROP DATABASE command failed because the database didn't exist. To change the workflow precedence, highlight the workflow arrow that connects Task 6 to Task 7, right-click, select Properties, then select Completion Success or Failure from the Precedence drop-down combo box.
Creating the FoodMart Database and Tables
You might wonder why I haven't recommended using the Access Upsizing Wizard to move the FoodMart database to SQL Server. Although the Upsizing Wizard, which became available in Access 95, is a helpful tool that easily migrates Access databases to SQL Server, the wizard doesn't work as well for large Access databases such as FoodMart. For these databases, you need to stage an Access-to-SQL Server migration in multiple steps similar to the steps in this example—creating the database, creating the database objects, loading the database, cleansing the data, and adding referential integrity. In deciding which utility to use, you have to take into account such factors as the underlying physical database design, table design, data type selection, and how much flexibility you have in determining when to move and cleanse data.
Task 7: Creating FoodMart. The script that Listing 5, page 52, shows creates the FoodMart2000_Master database that appears in the Data Files tab on the FoodMart2000 Properties window in Figure 10, page 52. Note that the database's size is 25MB, expandable by 10MB. Although the database can grow to 35MB, it reclaims this space when you issue a DBCC ShrinkDatabase operation from the cleanup task. Again, I used an ActiveX Script task rather than an Execute SQL task to specify at runtime the database name and the directory in which I wanted to create the new database files. I used the scripting task because DDL statements don't support parameter markers.
Prev. page
1
[2]
3
4
next page 
