Solved

DTS Package Copy ( repeating)

Posted on 2006-10-31
13
473 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 30
SqlServer no dupes 25 34
T-SQL Default value in Select? 5 26
T-SQL: New to using transactions 9 25
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

773 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