DTS Package Copy ( repeating)

I have a LIVE server with many of the developers DTS packages residing locally.

I know how to run a one off hit via DTS to copy the packages down to the DR site however as these packages are constantlt updated I need a way to refresh my DR servers DTS packages on a weekly / nightly basis. ANy idea? I've had a look at sqldts.com but this doesn't really cover what I was looking for. I basically want some sql / dts to be called every night by a job to transfer packages across a network to a DR (linked server). The link is already setup.
LVL 5
NetstoreAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
RickBeebeConnect With a Mentor Commented:
Yes, acperkins, I believe the task is to automatically refresh the DR server when the source server DTS packages change.

Do you have a simplified approach using only SQL Server 2000 tools.

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

http://www.sqlservercentral.com/columnists/awarren/copyingdtspackagestoadifferentserver.asp
0
 
NetstoreAuthor Commented:
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
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
RickBeebeCommented:
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
0
 
Anthony PerkinsCommented:
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
0
 
NetstoreAuthor Commented:
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??
0
 
NetstoreAuthor Commented:
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
0
 
RickBeebeCommented:
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
0
 
Anthony PerkinsCommented:
Are you attempting to update all the packages?
0
 
Anthony PerkinsConnect With a Mentor Commented:
You could try deleting all the packages first, but make sure you have a good backup first.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.