18 November 2005

DTS to SSIS Migration

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.

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:

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.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 242081 times – thanks for reading.

Tags: , , , , ,

  • Rate
    [Total: 154    Average: 3.5/5]
  • Share

Allan Mitchell

View all articles by Allan Mitchell

  • Anonymous

    getting error code 0x04BF318C
    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

  • Anonymous

    Upgrading DTS packages to SQL Server Integration Services
    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?

  • Anonymous

    Upgrading DTS packages to SQL Server Integration Services
    Never mind. I see in the html link – step-by-step examples they are saved as DTSx files.

    My apoligies.

  • Anonymous

    Upgrading DTS packages from sql server 2000 to sql server 2005
    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

  • Anonymous

    I forgot the ssis package password
    I forgot the ssis package password. How can I reset the password. Please help.

    Thanks

  • Anonymous

    Migrate DTS to SSIS
    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.

  • Karthikkr26

    SSIS Package Error
    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

  • Anonymous

    Really Usefull
    This Article is really usefull.

  • Sunil

    Migrate DTS 2000 Packages to SSIS
    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

  • Nayan

    DTS xChange – The Best Migration tool for DTS to SSIS Migration.
    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.

  • Anonymous

    Where is my DTS packages?
    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.

  • Anonymous

    3rd Party tool???
    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

  • Anonymous

    3rd Party tool???
    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

  • Anonymous

    Great links for comparison between MS Wizard vs DTSxChange
    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

  • siddu

    dts to ssis
    please anyone can help me i need dts to ssis detailed migration process.during the migration what type of issues will come

  • Priya

    Migrating DTS packages to SSIS Packages
    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…

  • Anonymous

    Migrate DTS 2000 Packages to SSIS
    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 = “\Lngdaysqld03vdSSelectdatasourceDownload.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

  • Anonymous

    Re: Great links for comparison between MS Wizard vs DTSxChange
    Is this the same product recommended by Microsoft?  http://www.dtsxchange.com

  • Convert

    Microsoft started recommending product for DTS to SSIS. Has anybody used it?
    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.

  • Anonymous

    Wow DTSxChange Rocks …
    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

  • Anonymous

    how to convert a script in dts to a script in ssis
    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.

  • drichard727@hotmail.com

    Where is my DTS packages?
    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

  • Nikunj Patel

    WOW!!! DTS xChange is a great product…..
    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

  • Anonymous

    Error installing DTSxChange
    Hi,

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

    -divaakar

  • Anonymous

    Where is my DTS packages?
    There are many people asking “where will we find the migrated DTS package in SSIS?” but nobody answered yet. Can you please help?

  • Sponge Bob

    Location of DTS package
    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

  • binod

    Can we convert DTSX package to DTS
    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…..

  • okiftequar

    Got an error message while doing DTS migration..
    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.

  • tinkureddy

    Thank you
    Thanks a lot it helped me a lot

  • amitanand

    To add a sign at the end of data of a column
    hi
    i need to add a sign(+/-) at the end of a column when loaded into db. What derived expression should i use to do that.

    i have a flat file with column abc having value 0000000237663- or it may be 0000000237663+.
    i need to load it in db with like 2376.63- or 2376.63+.
    its a numeric(14,2) filed

    regards
    amit