DTS IN SQL 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

DTS IN SQL 2005

Author

Topic

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-06-07 : 03:50:33

i have dts package in sql 2000. now server is getting upgrade as sql 2005 .
can i use the same dts package in sql 2005 ???
while doing upgrade ,directly will it convert to support sql 2005 support???
do i need to include DTS in SSIS ???
Windows authentication is enough to do this process??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-07 : 04:20:37

You can execute DTS packages in 2005 also. However for modifying a DTS package you will be asked to upgrate them to SSIS which may not always give you correct result.And if you want to modify them without migrating them to SSIS, you need to install sql 2000 DTS Designer.

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1251587,00.html

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-06-07 : 04:55:39

i dont do any modification , same process should run in sql 2005. that enough.if so i need to install sql 2000 DTS Designer ???

or

i need to import the dts package to sql 2005 ?? there is one option in SSIS . can i do that ???

I have sheduled this dts via bat file,can i do samething if it moves to SSIS ???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-07 : 06:05:46

quote:
Originally posted by niranjankumark

i dont do any modification , same process should run in sql 2005. that enough.if so i need to install sql 2000 DTS Designer ???

or

i need to import the dts package to sql 2005 ?? there is one option in SSIS . can i do that ???

I have sheduled this dts via bat file,can i do samething if it moves to SSIS ???

If you just want to execute them you dont require designer.The existing DTS packages will be under Management > Legacy > Data Transformation Services in management studio object explorer when you perform an in place upgrade. The link clearly describes about this.

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-06-07 : 06:50:06

i cud not find the link ..
do u say sql server management studio > ?? after that it will query analyzer right ...

where the legacy ?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-07 : 09:46:26

Since you are upgrading , Why don't you migrate DTS to SSIS package as well? You can use Execute SQL 2000 DTS like Visakh suggested but there is limitation when you use in SQL 2005 like Active X script (doesn't support COM object).

mdubey
Posting Yak Master

133 Posts

Posted - 2008-06-09 : 09:55:06

You are correct Sodeep, for Active x Script you need to write code in .net. You can not simply migrate from SQL 2000 DTS to SSIS. But if you want to run from 2005 your DTS(2000) Here is the detail.

1) Connect to the server(NEW inSQL 2005)
2) Go to the Management.
3) Under Management Click on the Legacy.
4) You should be able to see Data Transformation Services.

Manoj
MCP, MCTS

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-09 : 16:36:53

or Use Script task

Manoj
MCP, MCTS
[/quote]

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-06-11 : 02:53:08

sudeep and dubey ..

i found the dts in sql 2005 thanks... need some more info...
i have used dynamic property task, execute sql task and transform task. this package migrated to ( or upgraded ) to sql 2005. i have not done any changes.. it will run once in a month. so by doing upgrade we dont need to change package to adopt in 2005 version?? we can see this in legacy..

how to migrate DTS to SSIS ??? anyother BOL ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 10:21:53

quote:
Originally posted by niranjankumark

sudeep and dubey ..

i found the dts in sql 2005 thanks... need some more info...
i have used dynamic property task, execute sql task and transform task. this package migrated to ( or upgraded ) to sql 2005. i have not done any changes.. it will run once in a month. so by doing upgrade we dont need to change package to adopt in 2005 version?? we can see this in legacy..

how to migrate DTS to SSIS ??? anyother BOL ?

You have a migration wizard available in Visual Studio by which you can upgrade DTS to SSIS. but many tasks which it cannot convert from DTS will be replaced by a script task which you might need to rewrite using new SSIS tasks sometimes to suit your requirement.

mdubey
Posting Yak Master

133 Posts

Posted - 2008-06-11 : 10:27:47

That's correct Visakh, Please try to read thru the below mentioned linked,

http://www.simple-talk.com/sql/sql-server-2005/dts-to-ssis-migration/
and
http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1282913,00.html

Manoj
MCP, MCTS

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-06-12 : 06:41:07

you both are correct ..

point from your link :
======================
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.

i am using dynamic properties task , execute sql task , transformation task . so i am in need to rewrite the package.

i am entirely knew to SSIS. atleast to convert the above three task how to do.. any link ???
i need to start from
buisness inleginent studio > SSIS ???

admin@binaryworld.net
Starting Member

2 Posts

Posted - 2008-07-04 : 13:36:21

DTS to SSIS Migration can be very tedious and time consuming.

I would recommend you to try 3rd party product called DTS xChange. I liked this product very much because... its full Migration package including Discovery, Conversion and Auditing of packages when you run them after migration. It will apply best practices while migrating your packages so if are new to SSIS then this tool will save you significant amount of time.

here is the link

[url]www.dtsxchange.com[/url]

Screenshots
[url]http://www.dtsxchange.com/dtsxchange_screenshots.asp[/url]

DTS to SSIS Migration FAQs
[url]http://www.dtsxchange.com/help/dtsxchange/dtsxchange.faq.asp[/url]

maninder
Posting Yak Master

100 Posts

Posted - 2008-07-04 : 17:12:12

Actually, you can View the PAckage, but cannot change the Package unless you have DTS Designer.[That is a CRUDE method to access DTS in 2005].
Yes the only way to Work around Migration is REDO the package in Business Studio-Integration Services Project.

Maninder
Database Painter.. Leaving strokes, that amaze! and inreturn, put value to your Work. I am a Database [Architect / Administrator]

svicky9
Posting Yak Master

232 Posts

Posted - 2008-07-07 : 04:01:54

and also you cannot schedule the Legacy DTS Package to run at non peak hours. Better Convert it to SSIS.

Vic

http://vicdba.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources