Click here to monitor SSC
  • Av rating:
  • Total votes: 150
  • Total comments: 29
Allan Mitchell

DTS to SSIS Migration

18 November 2005

Upgrading DTS packages to SQL Server Integration Services

Determining how you will upgrade your DTS packages to SQL Server Integration Services (SSIS) is the first step in creating a DTS-to-SSIS migration strategy.

To start the upgrade, run the SQL Server 2005 Upgrade Advisor, which has an option specifically for DTS. Since everything has changed between DTS and SSIS, the upgrade will not be easy, but that shouldn’t concern you as long as you understand the process and what you’ll need to do going forward.

What happens during the upgrade?

When you upgrade an existing SQL Server instance, DTS runtime remains in place. Your DTS packages are not affected and local server packages remain stored in the msdb.dbo.sysdtspackages table. Structured storage files, or packages saved to DTS files, are also unchanged.

If you use Meta Data Services, previously called the Repository in SQL Server 7.0, you may encounter some problems. Meta Data Services is not supported by SSIS, which is not surprising considering that SQL Server 2000 Service Pack 3 removed Meta Data Services as a storage location. This is not a significant change, however, since the existing DTS object model and the DTSRUN command-line tool continue to support Meta Data Services.

The most important thing to remember about the DTS-to-SSIS upgrade is that after it is complete, your packages will continue to run. Scheduled jobs will still use DTSRUN and call the same packages from the same locations as they did prior to the upgrade.

When making the upgrade, you will need to determine how you will manage these legacy DTS packages in the future. The DTS runtime will continue to be available, and will have been updated to enable DTS packages to connect SQL Server 2005 data sources. But the DTS designer components are removed along with SQL Server 2000 Enterprise Manager.

You cannot connect to a SQL Server 2005 instance using the SQL Server 2000 Enterprise Manager. But SQL Server Management Studio, which is the SQL Server 2005 replacement for Enterprise Manager, supports DTS packages.

Downloading designer tools

In the Object Explorer window, under the Management Legacy nodes, you will find Data Transformation Services. This is the equivalent of local packages, and is the same table as mentioned above. You can import packages and start the DTS-to-SSIS migration wizard from there, but to do any editing work or manage packages you must download and install the Microsoft SQL Server 2005 DTS Designer Components. These can be found at the Microsoft download center as part of the feature pack for SQL Server 2005.

With the full DTS Designer, you can now create or edit DTS packages as you have done in the past. SQL Server Management Studio does not support Meta Data Services, so you will not be able to enumerate or edit packages stored there. As previously discussed, Meta Data Services will continue to be available through the object model and DTSRUN, but there is no support from management tools.

If this is problematic for you, move your Meta Data Services packages into the Local Packages store or files for easy maintenance. Without tools, DTSRUN can be used to save packages to a file: DTSRUN /Sserver /E /Npackagename /FX:\packagename.dts. Execute Package Tasks will need updating to reference the new storage location you choose.

On machines with multiple instances of SQL Server 2000, upgrading one instance to SQL Server 2005 leaves the remaining instances unchanged. The SQL Server 2000 tools, including Enterprise Manager, are unchanged as well. Since the original designer supplied with SQL Server 2000 will be intact, there is no need to install the Microsoft SQL Server 2005 DTS Designer Components. This ensures that you retain full management capability of the SQL Server 2000 instance, which will be shared with SQL Server Management Studio.

An important thing to remember is that you do not have to tackle high-risk upgrades or migration immediately. Eventually you will want to migrate DTS packages to integration services packages. The migration wizard would seem like the obvious route, but because of fundamental differences between the two architectures, it is not a trivial process.

Making the move

Let’s look at what can happen if you use the Package Migration tool to move your DTS 2000 packages to SSIS 2005. Keep in mind that a complex DTS 2000 package that involves a lot of glue code and performs some interesting things with the workflow cannot be plugged into the wizard with the expectation that it will come out the other end looking like an SSIS equivalent. It can’t. What you get instead is a best-effort attempt at the upgrade.

As an example, we’ll upgrade a package that can be found on our web site at http://www.sqldts.com/default.aspx?246. It covers object model manipulation, global variables and data pump tasks, all of which present certain challenges to the migration wizard.

When we upgraded the package, our first impression was that the results looked good. Upon further inspection, that was not the case. Let’s have a look at that and discuss how we migrated the package using the wizard. You can follow along by clicking this link: http://wiki.sqlis.com/default.aspx/SQLISWiki/MigrationWizardSteps.html.

Here are a few ways to locate the wizard:

  • On my PC I navigated to E:\Program Files\Microsoft SQL Server\90\DTS\Binn and located the executable DTSMigrationWizard.exe.
  • From within an SSIS project, right click on the SSIS packages folder and click on Migrate DTS 2000 Package.
  • In SSMS, expand the database server, expand management, and then expand legacy. Right click on the data transformation folder.

When you launch the wizard, you will see a splash screen. Click next to retrieve the DTS package. In our example, we chose a structured storage file, but it could easily have been a SQL Server package. Click next to specify to what location you want to save the migrated package. Again, you can store the package to SQL Server, but we have chosen to go to the file system.

The result

The storage file could contain more than one package, so on the next screen we select the packages we want to migrate. In our example we only have one package, which is password protected. On the next screen we specify the password status. Notice that the package moved into the box of authenticated packages. It is useful to have a log of what happens with your package, and the next screen enables you to specify a log location.

The last screen in the wizard presents a checklist of what it will do before attempting the upgrade. Once the package has been migrated, we can look at it in the Business Intelligence Development Studio. As mentioned earlier, the conversion looked good on first inspection, but the package would fail every time because the active script tasks make constant reference to step names, and these cannot be resolved when we execute the package. The error we see is:

Error: 0xC0048006 at DefineTheGVs, ActiveX Script Task: 
Retrieving the file name for a component failed with error code 0x000F3D04.

The global variables in the DTS 2000 package have been successfully migrated, as has the separation of the connection manager from the source adapter. This package could be fixed, but there is another glaring error. SSIS introduces the Foreach Enumerator, which is easier to use than Active X Script, and is more robust and easier to debug. If we wanted to migrate this package, it would be best to rewrite it from scratch rather than using the wizard.

There is another well-used strategy of the migration wizard that is not shown in our example: Preserve the DTS package task and encapsulate it in a new special package. This package will contain tasks that cannot be migrated at all, and are then embedded within an Execute DTS 2000 Package Task, which is added to the new SSIS package. This keeps the DTS elements working and within DTS, and calls them as required from the new SSIS package.

Conclusion

The migration wizard will try to use an SSIS equivalent to a DTS task. Sometimes this works and sometimes it does not, as illustrated in our example. The Active X Script task in DTS is the same in SSIS. What is not the same is the way you manipulate the object model from within. The transform data task and the data flow task are similar in DTS and SSIS, and the wizard does an excellent job of migrating that structure along with connection managers.

There are tasks that have no equivalent. One that comes to mind is the dynamic properties task in DTS. It does not exist in SSIS, and the equivalent in SSIS would be to use configurations or expressions, which are now part of the engine and package architecture as opposed to specific tasks.

You may decide to leave DTS running alongside SSIS. We covered the runtime and management sides of DTS within SQL Server 2005, so if the migration wizard is not working for you or you lack test resources to complete such a project, this would be a viable option.

Darren Green is the founder of SQLDTS.com. After a brief experience with SQL Server 6.5 and BCP, he moved onto version 7.0 and the joys of DTS. He is a SQL Server MVP and can frequently be found in the Microsoft public newsgroups. In his spare time he is lead DBA for one of the top independent financial advisor portals in the U.K.

Allan Mitchell began working with SQL Server version 6.5. He is an MCSE on Windows 2000, an MCDBA and a Microsoft SQL Server MVP, and consults on DTS for an array of clients in the U.S. and Europe. He is the co-author of <A href="http://www.sqldts.com/?3001>Real-World SQL-DMO for SQL Serverand has been published in SQL Server Professional Magazine.

Allan Mitchell

Author profile:

Allan Mitchell began working with SQL Server version 6.5. He is an MCSE on Windows 2000, an MCDBA and a Microsoft SQL Server MVP, and consults on DTS for an array of clients in the U.S. and Europe. He is the co-author of Real-World SQL-DMO for SQL Server and has been published in SQL Server Professional Magazine.

Search for other articles by Allan Mitchell

Rate this article:   Avg rating: from a total of 150 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: getting error code 0x04BF318C
Posted by: Anonymous (not signed in)
Posted on: Wednesday, November 22, 2006 at 7:17 AM
Message: Hi,
I am getting the following error when i debug my SSIS package.

Steps: Manually copied and pasted the Active X script from DTS to SSIS. Which should get fired on Error event of the failure to read the source flat file. This script does two things 1. It creates a log.txt file in a specified folder using the FSO object.

Debugging result: [ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x04BF318C

Any thoughts on this issue.

Thanks
MVM

Subject: Upgrading DTS packages to SQL Server Integration Services
Posted by: Anonymous (not signed in)
Posted on: Thursday, January 11, 2007 at 4:28 PM
Message: Your article is about as helpful as any MicroSoft Help file I have read. NADA

Where do the migrated files go? After you run the wizard where ARE the DTS packages?

You mention "the above database" and I checked the msdb.dbo.sysdtspackages table - can't open it.

So where ARE the packages?


Subject: Upgrading DTS packages to SQL Server Integration Services
Posted by: Anonymous (not signed in)
Posted on: Friday, January 12, 2007 at 9:38 AM
Message: Never mind. I see in the html link - step-by-step examples they are saved as DTSx files.

My apoligies.

Subject: Upgrading DTS packages from sql server 2000 to sql server 2005
Posted by: Anonymous (not signed in)
Posted on: Sunday, April 15, 2007 at 12:54 PM
Message: I successfully completed migration of DTS packages from sql server 2000 to sql server 2005. but afer migration i am not able to see dts packages.

Is there any step still pending to view this dts packages.

thank you

Subject: I forgot the ssis package password
Posted by: Anonymous (not signed in)
Posted on: Tuesday, May 15, 2007 at 7:42 AM
Message: I forgot the ssis package password. How can I reset the password. Please help.

Thanks

Subject: Migrate DTS to SSIS
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 23, 2007 at 10:28 PM
Message: Use Visual Studio 2005 Integration Services you will find the converted dts pacakages as dtsx. Dts packages will get lost if one starts Migration Wizard from within a db server under legacy node.

Subject: SSIS Package Error
Posted by: Karthikkr26 (view profile)
Posted on: Friday, June 01, 2007 at 5:18 AM
Message: I hv migrated DTS Pkgs to SSIS and in one of the ActiveX Script Steps i use
"dim pkg=DTSGlobalVariables.Parent"
When i execute this step then i get this error below:
"[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code"
Could u help me on this plz
Thanks!
Karthik

Subject: Really Usefull
Posted by: Anonymous (not signed in)
Posted on: Thursday, September 27, 2007 at 12:23 PM
Message: This Article is really usefull.

Subject: Migrate DTS 2000 Packages to SSIS
Posted by: Sunil (view profile)
Posted on: Monday, October 08, 2007 at 11:23 PM
Message: I successfully completed migration of DTS packages from sql server 2000 to sql server 2005. but afer migration i am not able to see dts packages.

Is there anything to be done to view these DTS Packages?

Please help me out i am in very much need of it


Subject: DTS xChange - The Best Migration tool for DTS to SSIS Migration.
Posted by: Nayan (not signed in)
Posted on: Thursday, November 15, 2007 at 12:01 AM
Message: Hi All,

Everybody knows that Microsoft inbuilt DTS Migration wizard has only 10-25% success rate depending on complexity of the package. If anybody looking for robust tool which can convert all your DTS Packages and apply Best Practices of SSIS then do not forget to look for product called "DTS xChange".

check this URL for detailed comparison between MS Migration Wizard vs DTS xChange

http://pragmaticworks.com/Products/compare/DTSxChange-vs-MSWizard.htm

Official URL of the Product is http://www.pragmaticworks.com/dtsxchange.htm


=========================
DTS xChange Product Description
=========================

Converts hundreds of DTS packages to SQL Server Integration Services (SQL Server 2005 or 2008) in moments

- Validates packages after execution to ensure that they will work upon execution
- Deploys packages automatically to SQL Server 2005 or 2008
- Handles nearly all tasks except for MSMQ and Data Driven Query Tasks
- Applies a series of rules on DTS packages:
- Logging to text or SQL files
- Enable checkpoints
- Enable transactions
- Logging through event handlers into a robust event table
- Migrate children packages automatically
- Consolidate duplicate connections
- Create configuration files automatically
- Create sequence containers from parallel tasks
- Profiles packages for cost (man hours and hard cost) of migrations
- Converts SQL Native Client connections
- Can handle ODBC connections as a source
- Convert UDL files to connection managers
- ActiveX Script Tasks are migrated to ActiveX Script Tasks in SSIS. Use the Profiler to determine quickly which objects are created inside the tasks and determine action items to migrate the task.


Subject: Where is my DTS packages?
Posted by: Anonymous (not signed in)
Posted on: Monday, January 07, 2008 at 10:32 AM
Message: I successfully migrated my DTS packages from sql server 2000 to sql server 2005, but where are they? I'm not able to see dts packages. Please help me, I have been searching the internet and I have found a lot of people asking the same question but I have not seen the answer.

Subject: 3rd Party tool???
Posted by: Anonymous (not signed in)
Posted on: Tuesday, January 22, 2008 at 11:37 AM
Message: Has anyone here taken a look at DTSXChange?? We are going to start the task of converting around 2000 packages, and DTSXChange was recommended to me by a friend, has anyone used this? Does it bring value over the MS tools?

Thanks!

Bill

Subject: 3rd Party tool???
Posted by: Anonymous (not signed in)
Posted on: Tuesday, January 22, 2008 at 12:42 PM
Message: Has anyone here taken a look at DTSXChange?? We are going to start the task of converting around 2000 packages, and DTSXChange was recommended to me by a friend, has anyone used this? Does it bring value over the MS tools?

Thanks!

Bill

Subject: Great links for comparison between MS Wizard vs DTSxChange
Posted by: Anonymous (not signed in)
Posted on: Monday, January 28, 2008 at 6:30 AM
Message: Guys... Just look at this and you will be amazed how much more DTSxChange offers.

I think this is must have it tool if you planning for DTS Migration.

HTML Version
============
http://pragmaticworks.com/Products/compare/DTSxChange-vs-MSWizard.htm

Word Version
============
http://binaryworld.net/main/downloads/DTSxChange-vs-MSWizard.doc

PDF Version
============
http://binaryworld.net/main/downloads/DTSxChange-vs-MSWizard.pdf

Subject: dts to ssis
Posted by: siddu (not signed in)
Posted on: Wednesday, February 13, 2008 at 12:29 PM
Message: please anyone can help me i need dts to ssis detailed migration process.during the migration what type of issues will come

Subject: Migrating DTS packages to SSIS Packages
Posted by: Priya (view profile)
Posted on: Tuesday, March 04, 2008 at 1:21 PM
Message: Hello Guys,

I am new to SSIS. Could you please help me in migrating DTS packages to SSIS packages. What are the important things to consider while migrating?

Please help me...

Subject: Migrate DTS 2000 Packages to SSIS
Posted by: Anonymous (not signed in)
Posted on: Thursday, March 20, 2008 at 4:41 AM
Message: I hv migrated DTS Pkgs to SSIS and in one of the ActiveX Script Steps i use

Function main()

Dim url, auth

set objHTML = CreateObject("HTMLCreator")
url = "http://psc744:6060/reportUtil/bin/reader.pl?file=misc%2Fexclusion.def&as=tstxt&numRows=ALL"
auth = ""
objHTML.URL url, auth

If objHTML.GetData() Then
bodyOfForm = objHTML.Body()
file = "\\Lngdaysqld03vd\SSelect\data\sourceDownload.txt"

objHTML.WriteToFile(file)
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If

End Function


When i execute this step then i get this error below:
"[ActiveX Script Task] Error: "[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x019F000C. "
Could u help me on this plz

thanks
shivank


Subject: Re: Great links for comparison between MS Wizard vs DTSxChange
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 02, 2008 at 3:42 PM
Message: Is this the same product recommended by Microsoft?  www.dtsxchange.com

Subject: Microsoft started recommending product for DTS to SSIS. Has anybody used it?
Posted by: Convert (view profile)
Posted on: Wednesday, April 02, 2008 at 3:56 PM
Message: Heres whats on the MS 2005 upgrade site:

Streamline Your DTS Conversions in SSIS

SQL Server Integration Services (SSIS) brings power and performance to SQL Server 2005 well beyond the capabilities of DTS. To streamline these conversions during your upgrade,
DTS xChange by Pragmatic Works Software offers a fast and easy way to manage higher quality DTS to SSIS conversions.

Subject: Wow DTSxChange Rocks ...
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 02, 2008 at 8:32 PM
Message: If you serious about DTS to SSIS conversion then checkout this tool called DTSxChange

http://www.dtsxchange.com/
http://dtsxchange.com/DTSxChange-vs-MSWizard.asp

Subject: how to convert a script in dts to a script in ssis
Posted by: Anonymous (not signed in)
Posted on: Thursday, April 10, 2008 at 11:01 PM
Message: Now iam using a vb script in dts.i want to re write the script in ssis.which language will support that and what are the neccessary changes.

Subject: Where is my DTS packages?
Posted by: drichard727@hotmail.com (not signed in)
Posted on: Saturday, April 12, 2008 at 5:31 AM
Message: I successfully migrated my DTS packages from sql server 2000 to sql server 2005, but where are they? I'm not able to see dts packages.

Please help me

Subject: WOW!!! DTS xChange is a great product.....
Posted by: Nikunj Patel (not signed in)
Posted on: Monday, April 21, 2008 at 9:52 AM
Message: Just to try this product, I downloaded the demo of DTS xChage today. The tool is so good that it converted my 10 of most complex packages in minutes (including validating and testing). The conversion it self doesn't take that long at all.
Since no one knew SSIS in out team, previously we we thinking about an SSIS consultant who provided quote of $90/hr if we have more then 100 pkges to convert. He will take approximately 1-3+days on individual package conversion depending on the complexity of the package. Now, you do the math. He will have to "LEARN" what each package is doing and then do the conversion because of no documentation on the DTS packages what so ever. We were thinking of thousands of dollars which was really "TOO MUCH" for my small company and really small IT department with really really small IT budget.
I am seriously thinking of buyinh this product for migrating our 200+ packages. We are happy that we won't have to pay lots of money for the manual migration and still we will have the SSIS best practices already applied in the SSIS packages. We don't have to worry about optimizing the SSIS packages again.
Seriously, in a nut shell, DTS xChange is the BEST PRODUCT out there in the market (far better then Microsoft Wizard).....

- Nikunj

Subject: Error installing DTSxChange
Posted by: Anonymous (not signed in)
Posted on: Wednesday, July 02, 2008 at 12:50 PM
Message: Hi,

I tried installing this product. The installation was complete, but the application could not open. Can you please advice

-divaakar

Subject: Where is my DTS packages?
Posted by: Anonymous (not signed in)
Posted on: Wednesday, August 06, 2008 at 3:38 PM
Message: There are many people asking "where will we find the migrated DTS package in SSIS?" but nobody answered yet. Can you please help?

Subject: Location of DTS package
Posted by: Sponge Bob (not signed in)
Posted on: Wednesday, August 13, 2008 at 1:29 PM
Message: Since I saw many people asking for location they are located either in a dstx file or localy on the server in SQL server 2005 depending on the option you selected during the wizzard.

You can select the destination to be either Microsoft SQL server or DTSX file. The default is server so thats why nobody found their file.

You can import to the project even if its on server just go to project / Add existing package and it will show your package on the server ;) ort just remigrate to file if you prefer

Took me a few try to figure out. Enjoy

Subject: Can we convert DTSX package to DTS
Posted by: binod (view profile)
Posted on: Friday, October 17, 2008 at 12:36 PM
Message: HI,

Currently i am using SQL Server 2000 Enterprise Manager with all DTS applications. I have received a .DTSX package, and i am really having problem to import or use the package in SQL 2000 enviroment.

Is there any option that i can use that DTSX package in SQL 2000 enterprise manager without installing SQL 2005/2008.

OR how can i create DTS package using the DTSX package that i have received.

Please assist.....

Subject: Got an error message while doing DTS migration..
Posted by: okiftequar (view profile)
Posted on: Thursday, December 04, 2008 at 3:43 AM
Message: Hi,
I gone through your given link of DTS migration wizard prcess its nice, but after getting final screen of migration i got an error.

Error Message:
TITLE: DTS Migration Wizard Error
------------------------------
Exception of type 'Microsoft.SqlServer.Dts.MigrationWizard.HelperUtility.DTSMWException' was thrown.
Click Abort to stop the migration of the current package.
Click Retry to retry the operation.
Click Skip to skip the migration of the current task and continue with the next task.
------------------------------
BUTTONS:
&Abort
&Retry
&Skip
------------------------------


What it mean i am not understood. Can you please help me out by this problem. why its come and where i am wrong.

Thanks for your assistance.
Regards,
m.i.

Subject: Thank you
Posted by: tinkureddy (view profile)
Posted on: Wednesday, March 18, 2009 at 11:58 PM
Message: Thanks a lot it helped me a lot

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.