h1

Plan your DTS Migration with DTS xChange Profiler

September 23, 2011

Converting DTS packages to SSIS can be a tedious and labor intensive undertaking.  If you are like some companies you now have possibly hundreds of these packages to convert and estimating the time and resources it will take to complete the conversion project can be a project in and of itself.  DTS xChange has a very cool feature that will help you accurately project the time and cost to completion of the DTS conversion project.  The DTS xChange Profiler allows you to select the DTS packages on a package by package basis.  So lets say your currently have 300 DTS packages but you will be upgrading only your data warehouse DTS packages, with DTS xChange you can specify only those packages used to load the data warehouse and not the entire 300 packages.  This will allow your to be much more specific with you project plan.  The first step in using the DTS xChange Profiler is to tell it where your packages are located, file system or SQL Server.

image

In this example I have my DTS packages stored in the file system. I navigate to the folder and click next.  On the next screen you will get a list of all the packages that are stored in the specified folder.  There are several ways to select the packages from this screen.  First is the check all and uncheck all buttons, which I think just about every wizard should have if you are allowed to multi select items in a list, it just makes life easier.  you can click one at a time or hold down the SHIFT key to select packages in a range.  You can also search the list for a specific package by using the filter above the list.

image

As you can see the selected packages appear in a list pane on the right hand side which also allows you to remove packages one at a time or all of them from the selected list.  Once all the packages you want to convert are selected click next.  After selecting the packages you wish to profile, you will be prompted tell Profiler how long each type of DTS task takes you or your team to to convert. The times will vary based on skill and experience with SSIS and DTS but Profiler has default estimates. You can also tell DTS xChange what the hourly rate for the SSIS developer is on this project and it will help calculate the cost per package.

image

Another thing you will notice is that the tasks listed in the left pane are color coded to indicate to what extent they can be converted.  Most of the tasks have no problem being converted, but some tasks can’t be converted because they are no longer supported in SSIS. Some tasks like ActiveX Scripts can be partially converted, and DTS xChange will review the objects that you are using inside your task and give you guidance on how to convert those tasks to a similar task inside of SSIS.  Once you are satisfied with your estimates click next and DTS xChange will generate a report for you to the level of detail you specified.   The following is a sample of the report at a detailed level.

image

image

image

image

This report can be exported to PDF or even an excel file.  This would be a huge help in not only creating a project plan but it can also give you the ability to get real numbers in front of decision makers.  All in all a very useful tool, and this is only profiling your packages, wait until you see it convert the DTS packages, but that is for another day.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: