June 7, 2008 at 1:53 am
I have dts package in sql 2000.now server is getting upgrade as sql 2005.
can i use the same dts package in sql 2005 without doing anything???
while doing upgrade ,directly will it convert to support sql 2005???
or Do i need to include DTS in SSIS ???
Windows authentication is enough to do this process??
June 9, 2008 at 7:50 am
Two bits you may want to check out:
From Microsoft upgrade guide:
Migrating to SQL Server 2005 Integration Services
There’s a reason that Microsoft didn’t use the name of Integration Services’ predecessor, Data Transformation Services (DTS), for its new SQL Server 2005 extraction, transformation, and loading (ETL) component. SQL Server Integration Services (SSIS) was a complete code rewrite—Microsoft didn’t bring one line of code over from DTS. With industry demands for faster performance and hardware consolidation to handle ever-increasing data complexity and volume, DTS wasn’t positioned as the long-term solution. So while DTS and SSIS are both ETL tools, architecturally they diverge greatly. Because of this, migration will require some redesign and solution changes so that you can leverage the compelling new SSIS features.
Moving from DTS to SSIS is a migration, involving wizard-driven output along with some manual redesign to complete the process. Some DTS tasks have a straightforward upgrade path to SSIS and are accommodated by the wizard. You might be able to use the wizard to upgrade other tasks depending on their use and design, but some tasks might be more difficult to upgrade or not upgradeable. Here’s what you can expect in upgrading your DTS packages:
• Simple data pumps. Data pumps created by the DTS Wizard or that are simple copy column transformations will be upgradeable most of the time.
• Workflow packages. If your packages are mainly workflow based, with tasks such as the Execute SQL task, they will have a fairly straightforward upgrade path and should also be handled by the wizard in most cases.
• Data pumps with transformations. If your developers used the DTS Designer to build packages that use data pumps with transformations, the wizard will encapsulate the old data pump task. The newly migrated package will invoke the old DTS object model at runtime. Before you upgrade your SQL Server 2005 installation to the next version of SQL Server, you'll have to replace this encapsulated functionality with the new SSIS constructs. Also in this category are Data Driven Query Tasks and Parallel Data Pump Tasks.
• Self-modifying packages. You’ll need to redesign DTS packages that leverage the DTS API to manipulate DTS objects and properties. For example, a common scenario is a looping construct in DTS, designed with scripts that use the API to modify wait states on other package steps. You can redesign this construct by using the built-in For Loop and For-Each Loop Containers in SSIS.
• Scripts tasks. A migrated ActiveX Script Task will run unless it tries to access the DTS runtime package objects. Variable access should use the new SSIS variables without redesign. But in general, you should still reevaluate script tasks against the new SSIS functionality and possibly rewrite them using new tasks.
• OLAP processing tasks. As with Data Transformation Tasks, the wizard will encapsulate these tasks, and they will run in SSIS. After the target server has been upgraded, consider replacing encapsulated functionality with the new SSIS functionality.
• Custom tasks. The wizard will encapsulate these custom tasks, and in many cases, they will run in SSIS. However, the best approach is to make new SSIS custom tasks or transformations.
• Dynamic property tasks. You’ll need to redesign these tasks in SSIS, using Expressions and Configurations to replace the Dynamic Property functionality.
Note that you'll have time to incrementally migrate packages over to SSIS. When you install SQL Server 2005, you have the option to install the runtime files required for DTS packages to execute on SQL Server 2005—without SQL Server 2000 needing to be installed. This makes the side-by-side migration a compelling story, especially in an environment where the DTS packages contain many tasks that require manual migration. SSIS also contains an Execute DTS package object when the runtime files or SQL Server 2000 has been installed on the SSIS server. A side-by-side implementation of SQL Server 2005 SSIS and SQL Server 2000 DTS will give you the greatest flexibility as you approach package migration.
And from Books Online:
http://msdn.microsoft.com/en-us/library/ms143706.aspx
"Got no time for the jibba jabba!"
-B.A. Baracus
June 9, 2008 at 9:26 am
Follow ahutch's last link to get to the DTS run-time components you'll need to run the legacy DTS packages in SQL 2005. I haven't done an in-place upgrade, but I suspect it will keep the DTS packages without trying to migrate them to SSIS. You'll see them in SSMS under Management->Legacy->Data Transformation Services.
Greg
June 10, 2008 at 10:43 am
It's relatively simple to run DTS packages under SQL2005 with legacy support. We decided to do that when we moved from 2000 to 2005 in order to keep the transition as straight-forward as possible.
We know that we want to move our relatively few packages (< a dozen) to SSIS eventually. That's going to be a bit more work than just running an upgrade wizard. SSIS has programming features not found in DTS that we'll want to use, and more importantly DTS has some features that don't translate directly to SSIS (like "Set Dynamic Properies"), so the wizard would fail and the process will involve manual work. Even if a package can be successfully upgraded to SSIS with the wizard, you may find yourself wanting to re-work it anyway. Some of the upgrade support built into SSIS for SQL 2005 is slated to be dropped in 2008 -- last I heard, ActiveX scripts (not "Script components" or "Script Tasks") won't be supported in SSIS 2008 -- so you'll end up re-writing anyway.
July 1, 2008 at 2:37 pm
Have to tried new Product called DTS xChange
It will make your life easy when you migrate DTS Packages to SSIS. The most interesting feature of this product is ... applying best practices of SSIS during the migration and Auditing Framework to Monitor your packages after Migration
Homepage
http://www.dtsxchange.com/[/url]
Comparison with MS Wizard
http://dtsxchange.com/DTSxChange-vs-MSWizard.asp
Screenshots
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply