Migrating DTS with Dynamic Properites to SSIS

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

SSIS and Import/Export (2005)

Migrating DTS with Dynamic Properites to SSIS

Author

Topic

bmassey
Starting Member

22 Posts

Posted - 2008-11-11 : 13:56:27

My company has developed some pretty complex DTS packages that heavily use the Dynamic Properties tasks. We have a migration to 2005/SSIS coming up so I've been researching the migration process from DTS to SSIS. The articles I've read seem to point towards the Dynamic Properties Task as a problem point for migration wizards or tools.

I'd appreciate any feedback in regards to what I can expect with this migration process.

Thanks!

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-12 : 05:08:50

If the DTS's are working you can import them directly as DTS packages. You need to download an update to view DTS's in 2005.
Once you have imported DTS packages here is a short procedure you can use to call them directly in Jobs.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Allows SQL Server 2000 DTS packages to be
--called and scheduled in SQL Server 2005 Jobs

CREATE proc [dbo].[s_RunDTS] @DTSName as varchar (250)
AS
DECLARE @cmd as varchar (1000)
SELECT @cmd = 'dtsrun /S "(local)" /U DTSUser /P DTSUserPassword /N "' + @DTSName + '"'

EXEC master..xp_cmdshell @cmd

-------------------

Usage:
In Job Step use TSQL Command:
Exec dbo.s_RunDTS DTSName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 06:40:46

you can maintain the DTS packages as it is in your sql 2005 also if you want.Refer below

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

bmassey
Starting Member

22 Posts

Posted - 2008-11-12 : 12:03:46

Thanks for the feedback gang! This link really helped.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 12:06:42

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources