Main | January 2007 »
November 2, 2006
Migrating your SQL 2000 DTS Packages to SQL Server 2005 Integration Services
By RDA Senior Software Engineer Joe ToscanoThis tip looks at how to use both the SQL Server 2005 Upgrade Advisor and the Package Migration Wizard to migrate your SQL 2000 DTS Packages to SQL Server 2005 Integration Services. Throughout this article SQL Server 2000 DTS will be referred to simply as DTS while SQL Server 2005 SSIS will be referred to as SSIS. We will see how to use the tools first for a very simple DTS package and then for a commonly utilized DTS Looping example. We will see that our simple DTS package migrates to SSIS while our DTS Looping example is another story. This article leverages the wealth of information found at www.sqldts.com and www.sqlis.com by referencing examples found at these sites.
The SQL Server 2005 Upgrade Advisor
Using the compiled knowledge from the product team, internal lab testing, and extensive SQL Server 2005 early adopter experience, Microsoft has developed an essential tool for your upgrade preparation called the Upgrade Advisor. The Upgrade Advisor is a free tool you can download that analyzes instances of SQL Server 7.0 and SQL Server 2000 in preparation for upgrading to SQL Server 2005 †without modifying any data on your servers. The Upgrade Advisor identifies feature and configuration changes that might affect your upgrade, and it provides links to documentation that describe specific issues and how to resolve them. The Upgrade Advisor analyzes the following components: Database Engine, Analysis Services, Notification Services, Reporting Services and Integration Services. We will focus on migrating DTS packages on a SQL 2000 instance to SSIS. For Upgrade Advisor information regarding download details, system requirements, and usage instructions, please use the following link:
http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75amp;displaylang=en
We will use the DTS Migration Wizard to migrate two DTS packages. The first DTS package is a simple traditional data transfer example while the second DTS package performs looping using Active X scripts and calls to the DTS 2000 Object Model.
DTS Package 1: Our Traditional DTS Data Transfer / Validation Example
I’ve designed many DTS Packages that extract and consolidate data from multiple sources and place this data into a SQL Server staging table. Since SQL Server is the ‘playground’ that I’m extremely comfortable with, once data is there I can validate, de-dup, consolidate, transform, resolve lookup values, handle slowly changing dimensions and so on. Under DTS, I typically employ stored procedures to accomplish many of these tasks. (Since SSIS supports a much larger number of features and much larger number of pre-baked tasks, most of this work can now be accomplished ‘in-line’ without external calls to stored procedures.) Our first Traditional DTS package does not utilize custom tasks, data driven query tasks, dynamic properties, or Active X scripts. It also does not make calls to the DTS Object Model. What should your strategy be if you need to migrate a package like this? We will address this in the sections that follow. Below is our Traditional DTS Data transfer package:
DTS Package 2: Our DTS Looping example
A common ETL task we may all have encountered is looping through a source directory to process files and moving the files to an archived directory. Since this functionality is not built in to DTS, it must be implemented using Active X scripts. These scripts utilize the File System Object to move processed files from an input directory to an archived directory. Further, these scripts dynamically change our data source to the file just discovered in a source directory. The looping is actually performed using the DTS Object Model to disable or enable steps depending on whether we’ve processed all of the source files in a source directory or if there are more files to process. Although our sample package has been modified slightly, the original DTS Looping Package is fully documented and can be downloaded from the following site:
http://www.sqldts.com/default.aspx?246
It’s quite possible that many folks who’ve had to loop in DTS used this example found in www.sqldts.com as their starting point, just as I did. What issues will you encounter if you need to migrate a package like this? We will address this question in the sections that follow. Below is our DTS Looping Package:
Invoking the Upgrade Advisor
The SQL Server 2005 is built on a rules-based engine. When you execute the tool, a simple wizard prompts you to select components on a local or remote server. Based on your selection, the wizard prompts you to identify details about each component, such as a specific DTS Package to analyze. I am providing the Advisor’s screen snapshots below. (For the sake of brevity, some screens were omitted.)
What SQL Server component(s) would you like to analyze ?
Are your DTS packages in SQL Server or saved as a file ?
The Upgrade Advisor’s Findings
Notice that the Upgrade Advisor Report that is generated categorizes the issues you may encounter as red potential show-stoppers or yellow informational warnings. (you can drill down on each item to display more detailed information.) Further, the Advisor tells us whether the issue needs to be addressed before or after you migrate. Let’s dissect each of the migration issues that have been raised:
- Meta Data Services In our report we are first told that you can no longer save SQL Server packages using Meta Data Services. The packages should be moved to SQL Server storage (in the msdb database) or to structured storage files before you upgrade. (We stored our DTS Packages in SQL Server’s msdb database.)
- SQL Server 2000 DTS Designer Components This issue is raised in case you want to modify your newly migrated Integration Services packages that may contain SQL Server 2000 DTS Legacy components. (Not all DTS components map to SSIS components.) What if by upgrading or uninstalling the last instance of SQL Server 2000 you’ve lost your 2000 DTS Development Environment? You can retain or restore these components by installing the special Web download “SQL Server 2000 DTS Designer Components†from the Microsoft Download Center before or after you upgrade or uninstall SQL Server 2000.
- Active X Script Code / DTS Object Model The upgrade advisor noticed that one or more packages contains Active X Script code that accesses the DTS Object Model. The SQL Server 2005 Integration Services (SSIS) Package Migration Wizard does not migrate Active X Script code that accesses the DTS object model . After migration, we must manually modify the resulting SSIS package to restore former package behavior.
Traditional DTS Package Migration Strategy
Based on the reports generated by the Upgrade Wizard, I chose to migrate the Traditional DTS Package using the Package Migration Wizard. Even though this package does migrate and then operate as an SSIS package under 2005, there are several issues with the final product that have been addressed below.
Step 1: Invoking The Package Migration Wizard
The Package Migration Wizard can be launched from multiple places. I chose to invoke the Migration Wizard from the Business Intelligence Development Studio referred to as BIDS throughout the remainder of this document. Launching the wizard from BIDS allows one to migrate the DTS package to an SSIS package file. This file can then be opened in BIDS where it can be tested, re-engineered if necessary, and finally deployed. I launched the Migration Wizard using Project -> Migrate DTS 2000 Package menu options. In the first menu I am asked to provide the SQL 2000 Instance that houses the DTS package(s) I wish to migrate. Below are the screen snapshots of the Migration Wizard:
What Instance contains the 2000 DTS Package(s) we want to migrate ?
Where will the newly migrated SSIS (.dtsx) file(s) reside ?
Choose the specific DTS package(s) to migrate
Step 2: Re-engineering the Traditional SSIS Package
Even though this DTS package did migrate, we are not taking advantage of SSIS features and not following the SSIS in-line Design Philosophy. Since SSIS supports such a large number of pre-baked transformations, why not make every attempt to avoid external calls to stored procedures? For example, the package can be redesigned to use the Lookup Task instead of calling a stored procedure to do this. Also, the Slowly Changing Dimension Wizard can be used instead of calling a Stored Procedure.
The Looping DTS Package Migration Strategy
This DTS Package should be re-engineered since there are Active X Scripts and calls to the DTS Object Model. Since SSIS supports industrial strength programming concepts we can use containers to perform the looping and dynamically change the source file to process the newly discovered file. Also, we can use the File System Task to move processed files from a source directory to an archived directory. It turns out there is an excellent example of an SSIS package that does exactly this located at www.sqlis.com. This example entitled ‘Looping over files with a ForEach loop’ can be found in the following link: http://www.sqlis.com/default.aspx?55
Conclusions
The Upgrade Advisor can be used to point out critical migration issues you may encounter with your DTS Packages. It can be used to help you gauge the level of effort required for your DTS Package migration. In some cases you may choose to perform the migration but leave some of the 2000 DTS components intact. Perhaps you then can start working on a new version that replaces these legacy components with an SSIS-only version that fully utilizes SSIS’s muscles. Keep in mind that just because the Advisor may give your DTS Package a thumbs-up you still may have post-migration work to perform to take advantage of SSIS features.
Review the features of SSIS carefully. Knowledge of these features is vital to successfully and optimally re-engineering your DTS packages.
Share:
Posted by Steve Mann at 9:27 AM | Comments (0)
Structured Error Handling in SQL Server 2005 for Developers
by RDA Senior Software Engineer Joe ToscanoImproving exception handling is one way to tune and improve Transact-SQL 2005 Stored Procedure Code. Although this was once widely thought to have been one of the weakest parts of writing T-SQL, now SQL Server 2005 provides support for structured error handling. This article first focuses on the basics of the new TRY... CATCH constructs and then looks at some sample 2000 and 2005 code that produce constraint violations using transactional code.
Exception Handling Before SQL Server 2005
In previous versions of SQL Server, you would handle exceptions by checking the @@error global variable immediately after an INSERT, UPDATE or DELETE and then performing some corrective action if @@error did not equal zero. Often, developers would duplicate this unstructured code resulting in repetitive blocks of code and combine it with GOTOs and RETURNs.
Introducing the SQL 2005 TRY … CATCH Block
Structured exception handing is used with many popular programming languages such as Microsoft Visual Basic.NET and Microsoft Visual C#. This is a tried and proven robust method that can make your code more readable and maintainable. The TRY block contains the transactional code that could potentially fail, while the CATCH block contains the code that executes if an error occurs in the TRY block. If any errors oc cur in the TRY block, execution is diverted to the CATCH block and the error can be dealt with while error functions can be used to provide the detailed error information. TRY…CATCH has the following abbreviated syntax:
BEGIN TRY
RAISEERROR (‘Houston, we have a problem’, 16,1)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() as ERROR_NUMBER,
ERROR_SEVERITY() as ERROR_SEVERITY,
ERROR_STATE() as ERROR_STATE,
ERROR_MESSAGE() as ERROR_MESSAGE
END CATCH
Notice that we are able to use these functions in place of local and/or global variables. These functions should only be used in a CATCH BLOCK and are explained below:
- ERROR_NUMBER() returns the number of the error.
- ERROR_SEVERITY() returns the severity.
- ERROR_STATE() returns the error state number.
- ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
- ERROR_LINE() returns the line number inside the routine that caused the error.
- ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
Let’s look at a simple example stored procedure under 2000 and then 2005’s exception handling:
Structured vs. Unstructured Exception Handing: A Simple Example
Let’s take a look at an example stored procedure we will code under 2000 and then 2005. Both procedures start with some very simple tables that contain constraints that our insert will violate. The table schema follows:
create table dbo.Titles
(TitleID int Primary Key identity,
TitleName nvarchar(128) NOT NULL,
Price money NULL constraint CHK_Price check (Price > 0))
create table dbo.Authors
(Authors_ID int primary key identity,
au_fname nvarchar(32) NULL,
au_lname nvarchar(64) NULL,
TitleID int constraint FK_TitleID foreign key
references Titles(TitleID),
CommissionRating int constraint CHK_ValidateCommissionRating Check (CommissionRating between 0 and 100))
create table dbo.Application_Error_Log
(tablename sysname,
userName sysname,
errorNumber int,
errorSeverity int,
errorState int,
errorMessage varchar(4000))
Exception Handling under SQL Server 2000
As you will see, this stored procedures contains the unstructured error handling we’ve been using prior to the arrival of SQL 2005.
create proc P_Insert_New_BookTitle_2K
(@TitleName nvarchar(128),
@Price money,
@au_fname nvarchar(32),
@au_name nvarchar(64),
@CommissionRating int)
as
declare @err int,
@tablename sysname
begin transaction
insert dbo.Titles (TitleName, Price)
values (@TitleName, @Price)
select @err = @@error
if @err <> 0
begin
select @tablename = 'titles'
GOTO ERROR_HANDLER
end
insert dbo.Authors (au_fname, au_lname, TitleID, CommissionRating)
values (@au_fname, @au_fname, @@IDENTITY, @CommissionRating)
if @err <> 0
begin
select @tablename = 'authhors'
GOTO ERROR_HANDLER
end
GOTO EXIT_Proc
ERROR_HANDLER:
ROLLBACK TRANSACTION
-- Log the error
insert dbo.Application_Error_Log (tableName, UserName, errorNumber, errorSeverity, errorState)
values (@tableName, suser_sname(), @err, 0, 0)
EXIT_Proc:
commit tran
We’ve seen this code before! The best we can say is: ‘At least we have exception handling.’ The statement below executes our 2000 Stored Procedure:
exec P_Insert_New_BookTitle_2K 'Red Storm Rising',16.99,
'Tom','Clancy', 200
When we execute the stored procedure with the provided parameters, the insert into the Authors table fails because of an invalid Commission Rating value. Our check constraint flags this invalid value and we see the following error:
Msg 547, Level 16, State 0, Procedure P_Insert_New_BookTitle, Line 23
The INSERT statement conflicted with the CHECK constraint "CHK_ValidateCommissionRating". The conflict occurred in database "Adventureworks2005", table "dbo.Authors", column 'CommissionRating'. The statement has been terminated.
The problem here is we could not stop this message from being sent to the client. So the burden of deciding what went wrong will be placed on the client. Sadly, in some cases this may be enough for some applications to not use constraints!
Let’s try this again but this time we’ll use the TRY … CATCH
Exception Handling under SQL Server 2005
In this new and improved procedure we see the TRY … CATCH block and structured error handling:
create proc P_Insert_New_BookTitle_2K5
(@TitleName nvarchar(128),
@Price money,
@au_fname nvarchar(32),
@au_name nvarchar(64),
@CommissionRating int)
as
declare @err int,
@tablename sysname,
@errormessage nvarchar(2000)
BEGIN TRY
begin transaction
select @errormessage = 'insert into Titles table failed',
@tablename = 'Titles'
insert dbo.Titles (TitleName, Price)
values (@TitleName, @Price)
select @errormessage = 'insert into Authors table failed',
@tablename = 'Authors'
insert dbo.Authors (au_fname, au_lname, TitleID, CommissionRating)
values (@au_fname, @au_fname, @@IDENTITY, @CommissionRating)
commit transaction
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION-- Log the error
insert dbo.Application_Error_Log (UserName, tableName, errorNumber, errorSeverity, errorState, errorMessage)
values (suser_sname(), @tableName, ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE())
RAISERROR (@errormessage, 16,1)
END CATCH
Notice the SQL 2005 exception handling code is much more streamlined and therefore more readable and maintainable. There’s no cutting and pasting of our exception handling code and no GOTOs. We see the results below when we execute this stored procedure:
exec P_Insert_New_BookTitle_2K5 'Red Storm Rising',16.99,
'Tom','Clancy', 200
When we execute the stored procedure with the provided parameters, the insert into the Authors table fails because of an invalid Commission Rating value. When this happens, execution is diverted to the CATCH block. Our catch block rolls back our transaction and inserts a row into our Application_Error_Log using the SQL2005 supplied functions.
Conclusions
The new TRY and CATCH blocks certainly make safe coding easier for handling errors, including stopping error messages from ever making it to the client. While it may take a mindshift for many T-SQL programmers, it’s a feature that was really needed. Keep in mind that by migrating your 2000 code to 2005, you may have to change your application also if it has already been designed to handle errors that are shipped to the client. I believe that this potential re-engineering effort would be worth it in the long run.
Share:
Posted by Steve Mann at 8:41 AM | Comments (0)
