Solved

DTS Package Copy ( repeating)

Posted on 2006-10-31
13
475 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

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…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

752 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