The DTS to SSIS Dilemma…

SQL Server Integration Services or SSIS, saying those words around IT personnel who have a plethora of DTS packages will shiver! Naturally, they tremble because of two key reasons. First, SSIS is a completely different and more powerful tool, with power does come a certain degree of complexity. DTS has been used for a range of tasks beyond simple Extract, Transform, Load (ETL) purposes. Recently I was at a client’s site that entailed over 400 DTS packages, few of which were used for actual decision support purposes. Analyst, Information Workers, and others that are common non-IT personnel users of DTS are going to feel awkward (at least at first) using SSIS!

The second reason that folks get nervous about any potential DTS to SSIS migrations is because they are to some degree aware of the migration ‘cost’ or effort involved. DTS to SSIS is not a trivial migration, especially for those clients who have the 100s or even 1000s of production packages. There is a package migration wizard I will discuss in part 2 of this post that ships with SQL Server 2005 and 2008.  The package migration wizard attempts to migrate your DTS packages, however this wizard roughly migrates only around 40% of the various possible package configurations!

So Why Migrate Given the High Effort Required?

This is a natural question and the answer is also simple: power and speed. First, SSIS is a much more powerful data integration platform. SSIS contains several features that the older DTS platform did not have. Here are some of the features of SSIS that DTS did not have (or at least to the degree that SSIS contains):        

• Separation of control flow and data flow        
• Some connections have been optimized        
• Several additional various transformations        
• Wider range of data sources        
• Security enhancements        
• Toolset unification via BIDS with the other BI projects, source control integration
• Checkpoints & in place restarts        
• Dynamic runtime configurations        
• Superior logging capabilities 

The second reason to migrate is speed or rather performance. SSIS entails two different yet cooperating engines, the Run-Time Engine and the Data Flow Engine. SSIS’s Data Flow Engine leverages a buffer-oriented architecture to load and transform data in memory. As a result, SSIS is fairly fast compared to DTS, especially in terms of transformations. If you are performing simple data loads without many transformations involved then there will be a small delta in performance leaning towards SSIS (SSIS’s SQL Server destination component is faster than the older DTS connections). That said, as you increase package transformation complexity, SSIS performance will continue to increase over DTS. In fact, on average SSIS is believed to be roughly 7 times as fast as DTS. Your organization may experience a lesser increase in performance or even higher but this is the average gain seen.

A third reason though it is not a prevalent one yet (which is why I did not mention it before) is because the DTS product is an official deprecated feature of SQL Server. What this means is that Microsoft is delivering a message to its customer that they need to migrate to the SSIS platform sooner than later. Some would argue with me that this should be the main reason why organizations should migrate but let’s be honest about the matter. SQL Server 2008 is the last version to support DTS (as of now) and thus as a customer you will have a long time until you are absolutely forced into this migration but note that the day will come.

We Are Already Running on SSIS 2005, Do We Need SSIS 2008?

This is where things get a bit more difficult. Do you really need to perform an upgrade to SQL Server 2008 just for the SSIS enhancements? Like most things in life, that will depend on your organization’s usage of SSIS and the larger SQL Server product. Product enhancements beyond SSIS are out of scope for this blog post but suffice it to say there are several BI related enhancements in the 2008 product beyond those found in SSIS. That said, below are the enhancements from SSIS 2005 to 2008:        

• New Data Profiling Task & Viewer        
• Enhanced Performance and Caching for the Lookup Transformation        
• Enhanced Data Flow Engine Tuned for Parallelism       
• Various smaller enhancements for supporting new data types and TSQL capabilities

OK, I Want SSIS…How Do We Migrate?

There are three core approaches to the DTS to SSIS 2005|2008 migration. I will be listing these solutions in order of ease. Also, only the last two are possible solutions if you must migrate immediately for some reason.·        

• Proactive

SolutionBased on the SQL Server Upgrade Advisor’s results, take corrective action on the existing DTS packages then re-scan them w/Upgrade Advisor. Once the Upgrade Advisor reports no outstanding issues leverage the 2005 Package Migration Wizard to automate the package migration process. Any packages that cannot have their upgrade issues resolved will need to be rebuilt from scratch in SSIS. 

• Reactive Approach w/Package Migration Wizard

Disregard the Upgrade Advisor’s alerts and use the Package Migration Wizard to perform the migrations. Review each new resulting SSIS package to ensure it is built correctly. For those packages which did not migrate correctly either modify the resulting SSIS package or create it from scratch in the SSIS designer. 

• Reactive Approach w/o Package Migration Wizard

Disregard the Upgrade Advisor’s alerts and rebuild all of the packages from scratch in the SSIS designer. 

Scalability Experts DTS to SSIS Effort Estimation Framework & Considerations

The first step in performing a DTS to SSIS migration project is the effort estimation itself. This is not a trivial migration process and thus warrants a short yet focused study on the effort required. At Scalability Experts we have developed a DTS to SSIS Effort Estimation Framework for such projects. The framework assists us in both the time and accuracy of our DTS to SSIS effort estimations. When you are performing a DTS to SSIS effort estimation some of the items you want to consider include:

• Package Complexities
• Package Upgrade Advisor Alerts
• Parent/Child Package Environments
• Package External Dependencies
• 64-Bit Environments

Summary & Part 2

In conclusion, DTS to SSIS is a somewhat difficult yet highly valuable migration for those organizations that have come to rely on the Microsoft data integration platform(s). With the arrival of SSIS 2008 there are even more reasons to perform the migration. Having a solid migration planned out and effort estimation phase completed will be key steps to ensuring a smooth migration. In part 2 of this blog post we will discuss the actual migration process itself (How to perform the upgrade). This is the next logical step in the process once management has given the thumbs up for the migration effort to continue. Cheers
Derek Comingore