Conversion from DTS to SSIS 2005/2008

Question

Conversion from DTS to SSIS 2005/2008

Anjan Wahwar

SSCommitted

Points: 1617
More actions
April 15, 2011 at 7:24 am

#249457

Hey,
I need to convert existing DTS packages to SSIS 2005 (or may be 2008) for my upcoming project. I have been working mostly with SSIS so not aware of DTS.
Can anyone who have worked previously on DTS to SSIS conversion help me out in preparing myself for the same. Mostly I am concerned with-
1) Do I need to learn DTS for successfully converting it. (If yes then any good resource)
2) What are the challenges that we might get while working with DTS to SSIS conversion. (This ofcource depends on the specific req but atleast I can get some idea from it)
3) What is the advantage/disadvantage in using Package conversion wizard for doing the same.
Thanks
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

I would not think you need to learn DTS per say but you should try opening a few and get familiar with them. A basic understanding should be enough. I converted most of our packages and in most cases is was enough to just figure out what the basics where. If you have complex DTS packages more may be needed

Some of the challaenges are for example if you had script tasks in DTS they are in ActiveX and there is no direct translation to the script task in SSIS. You may also find as I have that some of the things in DTS that could be accomplished with ActiveX can not be done in SSIS and you have to determine how to work around those problems.

I have not tried using any tools to upgrade the packages automatically but I have also not heard great things about them.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Anjan Wahwar SSCommitted Points: 1617 More actions · Answer 2

Thanks Dan. Its really helpful !!! 🙂

__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

I agree, you don't need to know DTS intimately. The wizard does ok, but will usually no do a very good job with even a slightly complex package or one that has a script component.

Keep in mind that in DTS in the ActiceX scripts you could manipulate the package as it was running, this is no longer true.

Also there are things done in DTS to handle looping that are much easier in SSIS.

One thing to be wary of is ActiveX field transforms on DTS data pumps. They are hard to find and don't upgrade well..

CEWII

Anjan Wahwar SSCommitted Points: 1617 More actions · Answer 4

Elliott Whitlow (4/15/2011)
I agree, you don't need to know DTS intimately. The wizard does ok, but will usually no do a very good job with even a slightly complex package or one that has a script component.
Keep in mind that in DTS in the ActiceX scripts you could manipulate the package as it was running, this is no longer true.
Also there are things done in DTS to handle looping that are much easier in SSIS.
One thing to be wary of is ActiveX field transforms on DTS data pumps. They are hard to find and don't upgrade well..
CEWII

Thanks for input Elliott.. So it seems that Active X scripts are something that needs extreme care while transforming existing DTS into SSIS.

__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!