Solved

DTS Package Copy ( repeating)

Posted on 2006-10-31
13
471 Views
Last Modified: 2013-11-30
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.
0
Comment
Question by:Netstore
  • 4
  • 3
  • 3
13 Comments
 
LVL 3

Expert Comment

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

http://www.sqlservercentral.com/columnists/awarren/copyingdtspackagestoadifferentserver.asp
0
 
LVL 5

Author Comment

by:Netstore
ID: 17842472
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
 
LVL 3

Expert Comment

by:RickBeebe
ID: 17842636
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17846441
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
 
LVL 5

Author Comment

by:Netstore
ID: 17848098
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 5

Author Comment

by:Netstore
ID: 17848442
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
 
LVL 3

Expert Comment

by:RickBeebe
ID: 17849495
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17849791
Are you attempting to update all the packages?
0
 
LVL 3

Accepted Solution

by:
RickBeebe earned 125 total points
ID: 17851601
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 total points
ID: 17851644
You could try deleting all the packages first, but make sure you have a good backup first.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now