DTS Package Copy ( repeating)

You can use this link, if you don't mind creating a VB app:

http://www.sqlservercentral.com/columnists/awarren/copyingdtspackagestoadifferentserver.asp

Thanks but I'm really looking at a one stop shop within SQL and I'll get my wrists slapped for creating anything in VB.
Cheers

This will get you half way there:  Export - (creates a structured storage file for all DTS packages using xp_cmdshell within SQL Server)

http://www.sqlteam.com/item.asp?ItemID=16512

This link should allow you to import the packages from the structured storage files: (Import)

http://www.nigelrivett.net/DTS/s_LoadPackageToServer.html

This is what we use (and no I am not referring to the first approach using DTSBackup 2000):

Transferring DTS Package:
http://www.sqldts.com/default.aspx?204

I tried that and it worked great the first time it ran but on subsequent runs it failed. This may be me as my DTS knowledge is weak at best.....should this be able to run on a daily basis or am I missing something obvious??

I've re-created this my package to transfer the DTS to the DR server and this ran without issue the first time. On a re-run of the package the following error is received:

It looks like duplicate package name


tep 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution. (Microsoft OLE DB Provider for SQL Server (80004005): The statement has been terminated.) (Microsoft OLE DB Provider for SQL Server (80004005): Violation of PRIMARY KEY constraint 'pk_dtspackages'. Cannot insert duplicate key in object 'sysdtspackages'.)
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

My guess is this is caused as the packages already exist in the target server, whats the best way to stop this, i.e truncate the sysdtspackages table as the first step in my DTS package?

Any help greatly appreciated

Modify the first script to create the following script to run before the import.  (delete existing packages)

dtsrun /S <servername> /E /N <packagename> /!D
...for each package

if you are not running using athentication then use:

dtsrun /S <servername> /U <username> /P <password> /N <packagename> /!D
...for each package

If you need help creating this script please let me know.

Thanks,

-Rick

Are you attempting to update all the packages?