| Downloads |
|---|
| 5964.zip |
We constantly comb support forums, user groups, and Web sites to keep abreast of the problems Data Transformation Services (DTS) developers face. This month, we look at several sources of confusion we’ve uncovered: timestamp data types, SQL Server licensing, and Microsoft Data Access Components (MDAC). In addition, we’ll tackle the tricky DTS lookups.
Timestamp Data Types
The proper use of SQL Server timestamp data types often confuses developers. We see questions such as, "If it’s really a timestamp, why isn’t it stored as a date?" and "What purpose does the timestamp serve?" Now that SQL Server 7.0 brings DTS into the picture, developers are asking a new question: "Why doesn’t DTS properly transfer my timestamp columns?" We’ll try to eliminate further confusion about timestamps.
First, you need to understand what the timestamp data type is. For starters, the SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that specify the relative sequence in which users enter data modifications in a database. Originally, Microsoft implemented the timestamp data type to support the SQL Server recovery algorithms. Every time a user modified a page, SQL Server stamped the page with the current @@DBTS (timestamp) value and incremented @@DBTS by one. This information was enough for the recovery process to determine the relative sequence in which users modify pages, but the timestamp values weren’t related to time. In SQL Server 7.0, the only remaining use of the timestamp data type is to maintain concurrency, because SQL Server increments the @@DBTS value only for use in timestamp columns. If a table contains a timestamp column, then every time someone uses an INSERT, UPDATE, or DELETE statement to modify a row, the timestamp value in the row is set to the current @@DBTS value. Then, the @@DBTS value increments by one. Because the local server generates timestamp values automatically and the values are unique within a specific database, you can’t apply a change to the timestamp value created at one server to the timestamp column at another server.
In DTS, you can’t insert or update a timestamp column. Consequently, a DTS package will fail if it tries to modify or insert data in an existing timestamp column. When it fails, the only error DTS reports is General Error –2147217887 (80040E21) Errors occurred, which isn’t very helpful.
When you use DTS to move timestamp data, you also might encounter a scenario in which the destination timestamp column doesn’t exist and the user relies on DTS to create the target table. In this case, DTS will execute successfully, but the resulting table won’t contain a timestamp column. Because the internal representation of a timestamp column is binary, when DTS creates the new destination table, it replaces the timestamp data type with the binary(8) data type. In addition, the new destination table will always allow nulls, regardless of whether the source timestamp column allows nulls. This subtle difference can wreak havoc if client applications depend on timestamp columns within the tables for concurrency control.
DTS, SQL Server Licensing, and MSDN
Has the SQL Server licensing trap caught you? If you’ve tried running DTS against SQL Server and received the error
The license for the installation of MS SQL Server on your source and destination connections does not permit the use of DTS to transfer data. Refer to your license for more information.
you’ve fallen into Microsoft’s trap. Many people, especially those who installed the SQL Server version that Microsoft ships with Microsoft Developer Network (MSDN) subscriptions, have seen this error. It occurs because Microsoft coded into its tools a check to validate the enforcement of its client-access licensing. The SQL Server End-User License Agreement (EULA), section 2, subsection E, describes how distributed operations are performed only against the Server/Enterprise editions running in the per-seat licensing mode—for example, distributed queries, replication, and DTS servers.
Fixing the error is easy: Sites need to change their server-licensing mode from per-server to per- seat. However, users can only install the MSDN versions of SQL Server in the per-server mode. Therefore, until Microsoft changes its licensing, MSDN subscribers who don’t have a retail copy of SQL Server are out of luck. Also, beware of one more catch: After you install the MSDN version of SQL Server, you can’t directly upgrade to the retail version. So, make sure you’ve saved all your packages, schemas, and data before you install the retail version because you need to uninstall the MSDN version first.
Pre-SQL Server 7.0 Installations, DTS, and MDAC
As advertised, DTS, acting as an OLE DB consumer, extracts data from any data source that acts as an OLE DB provider (i.e., a data source that offers a native OLE DB interface) or any data source that offers an ODBC interface. DTS also accesses SQL Server 6.5 and earlier. The difference between accessing SQL Server 7.0 databases and its predecessors’ databases is the data-access method. SQL Server 7.0’s native interface is OLE DB, whereas you can use only ODBC to access previous versions.
Therefore, many organizations use DTS to extract data from and move data to older installations. What some organizations don’t know, however, it that there’s a catch in getting DTS to work properly against pre-SQL Server 7.0 servers. The catch lies in the server’s level of Microsoft Data Access Components (MDAC) support. MDAC is Microsoft’s packaging title for its data-access technologies, ADO, Remote Data Services (RDS), OLE DB, and ODBC. When you use these data-access methods with SQL Server, they rely on system-stored procedures and catalogs to provide the metadata about the server. As MDAC evolves, so must these procedures and catalogs. Each release of MDAC ships with a script, instcat.sql, which contains the most recent version of these catalogs.
DTS relies on version 2.1 (or later) of the MDAC technologies to access SQL Server. Coincidentally, this is the MDAC version that ships with SQL Server 7.0. MDAC technologies are compatible with earlier versions of SQL Server, as long as the system procedures and catalogs are updated. To upgrade the system catalogs, log in to your SQL Server as sa, change to the master database, and execute the instcat.sql script. Depending on the version of your server, the instcat.sql execution might generate many error messages. However, if the final set of messages informs you that the execution was successful, you can ignore the error messages. (Make sure you have a current backup before you modify the master database.)
DTS Lookups
A DTS lookup lets developers perform a query from within a transformation script and use one or more parameters to retrieve data. The transformation then consumes these results. For example, you can use a lookup to validate a particular code value or to map an existing value from the source data store to a new value in the destination store. Although you can use COM objects, variants, and global variables to accomplish similar tasks, the lookup offers some advantages. It uses an established connection that’s optimized for quick data retrieval, and it eliminates the need to hard-code values within transformation code.
To understand how you can use a lookup, consider the following example: A large health-care organization has a data warehouse that contains patient and case-related data from each of its member hospitals. One column in each data set contains a code that identifies the data source. However, for reporting purposes, you need to remap this hospital-dependent site code to the predefined corporate value. You can use a DTS lookup within the transformation script to map this code.
Before you code the transformation, you need to establish the lookup. You use lookups from within a Data Pump task or Data-Driven Query task. For this example, you need to use a Data Pump task. In the Data Transformation Properties dialog box, click the Advanced tab, as Screen 1 shows, then select lookups. This action opens the Data Transformations Lookups dialog box, which lists all available lookups, as Screen 2 shows. Click the Add button to add a Lookup.
Now you need to establish the Lookup. In this example, we named the lookup SiteCode. Next, you need to define the connection, which is the access method that DTS will use to perform the lookup query. Note that the connection the lookup uses can’t be the same one the source or destination data stores use. Finally, you need to define the parameterized query that will return the data. To do so, click the "…" button under the query column to open the query designer. The Data Transformation Services Query Designer dialog box, which Screen 3 shows, defines the query that we used. The question mark serves as a placeholder for the runtime parameter.
After defining the Lookup, return to the Data Transformation Properties dialog box, select the Transformations tab, and define the transformation. Listing 1 shows the transformation script to execute the Lookup and map the hospital-specific site code to the corporate value. When you edit a transformation script that contains a Lookup, you can parse the script. However, you won’t be able to test the script. Because VBScript relies on late binding to resolve all external object references, parsing the script is possible. But you can’t execute the script because it can’t resolve the Lookup.
We’ll Be Watching
DTS offers many functions that make importing and exporting data less daunting. However, like other first-generation tools, DTS has its share of bugs, problems, and features that cause headaches for even the most seasoned developer. During the next few months, we’ll continue to throw light on the development pitfalls within SQL Server and DTS.
