Moving DTS package from sql 2000 to sql 2005

Question

Moving DTS package from sql 2000 to sql 2005

rambilla4

SSCrazy Eights

Points: 8715
More actions
June 30, 2009 at 12:25 pm

#155893

Hi ,
We have upgraded sql server 2000 to sql server 2005 using side-by-side method. In this process we moved one DTS package from SQL 2000 to SQL 2005 as below:
1. Connect to sql server 2000 enterprise manager->Data Transformation Services->local pages->pakage1->open->save as-> selected the destination sql server 2005->finish.
2.In sql 2000 , we have a job which executes this DTS package. The step is dtsrun /S SQL1 /E /N Import/A gstrServer:8=SQL1I /A gstrClient:8=CF /A gstrEnvironment:8=Prod /A gstrPath:8="D:\"
3..Now I'm able to see this package in SQL Server 2005->management->legacy->Data Transformation Services->package1
4. Now I created the job step same as in SQL 2000, just replacing the new server name SQL2. i.e dtsrun /S SQL2 /E /N Import/A gstrServer:8=SQL12 /A gstrClient:8=CF /A gstrEnvironment:8=Prod /A gstrPath:8="D:\". And the job is running fine.
My Question is:
After the upgrade, the databases are still in compatibility mode 80. If we change the compatibility to 90, do I need to change in thing in the job step below?? or It will work as it is working in compatibility mode 80??
dtsrun /S SQL2 /E /N Import/A gstrServer:8=SQL12 /A gstrClient:8=CF /A gstrEnvironment:8=Prod /A gstrPath:8="D:\"
thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

I think you will be fine, provided that the queries executed behave the same way against the database in that compatibility mode. I would probably upgrade the DTS to SSIS if at all possible using the Migrate Package option in BIDS. If you have ActiveX script tasks it WILL be sketchy. Also if you use UDLs, then you will need to get them converted to other connections before it will really work. You can get a good tool to help at:

http://www.sqlbi.eu/Home/tabid/36/ctl/Details/mid/374/ItemID/4/Default.aspx

It is called DtsToSsisPrepare, and it works pretty good.

CEWII

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 2

Hi Rambilla,

Those jobs work fine with 90 compatibilty mode.

Thanks

Yours

SQL Buddy.

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 3

The DTS package we have is very complex one and has ActiveXscript tasks and Dynamic properties Tasks. So for time being, we just want use the same DTS in SQL Server 2005.

Thanks

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

Hi Rambilla,

That's a clever move. But eventually convert that in to a SSIS package.

Thanks

Yours

SQL Buddy

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

One of the things I have experienced is that a lot of stuff that I used dynamic properties tasks and ActiveX for can be taken care of with the provided components, not all, but many.

CEWII

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

One of the things I have experienced is that a lot of stuff that I used dynamic properties tasks and ActiveX for can be taken care of with the provided components, not all, but many.

CEWII