Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DTS: Copy Objects Task

By Bruce Szabo,

Overview

Microsoft has provided so many great tools and at times they have helped solve many issues. There are cases though where the tools do not behave exactly as one would expect. There is nothing more frustrating than finding a tool that is supposed to help, actually causing an error or behaving correctly but in an unexpected manner. This article explores a real world case where the DTS Copy SQL objects task caused me hardship while trying to solve a problem.

The SQL Copy object is used within DTS. In its most rudimentary form it can be used to transfer objects between servers or between databases on the same server. In some cases I have used this object to bring a production copy of my database to a local testing environment and in other cases I have used it in production to move staged tables into a production.

When SQL Copy Objects Task Doesn’t

The Problem

One of the issues I encountered with the SQL Copy task occurred when I wanted to do the following. I wanted to copy objects from Server 1 (a staging area) where I have two databases Northwind and Southwind to Server 2 (a production server) where I have the same databases. The two databases contain tables with the same name authors and publications, as shown below, and these were the objects that I wanted to copy. It seemed pretty straight-forward and the SQL Copy object task seemed like a perfect tool for the job.

To accomplish the task I created two DTS packages with nothing more than the SQL Copy task in the package. The DTS packages looked like the one below. One package moved the tables (authors and publications in Northwind) from Server 1 to Server 2 while an identical package copied the same tables from Southwind on Server 1 to Southwind on Server 2.

When I ran two separate DTS packages that copied the two tables to the same databases on a production server the publications table was empty in one of the databases. The same phenomena occurred when I tried to copy the tables to databases on separate servers. For instance, even if I put the Southwind database on Server 3 it was still empty.

I tried a number of different things to see if I could get this to work. One solution was to put both tasks in the same package. This only worked if I made sure that one task finished before the next one started as shown below.

No matter what I did I could not get this to work unless I waited for one task to finish before the next copy began. It took a little time and some help from others, but eventually we figured out what was wrong. Once you know how the SQL Copy objects task works it is pretty easy to see what was happening.

The copy task scripts the creation of the objects and then if specified it bulk copies data into any tables and applies indexes. If you look at the properties of the task you will see 3 tabs (Source, Destination and Copy).

Closer inspection of the copy tab shows an option to change the script directory. If this directory is changed for each of the two copy tasks, one for the Northwind copy and another for the Southwind copy everything worked. If I inspect my target directories (the ones I specified on the copy tab) after a copy the issue seems obvious. In the following figure all the needed files that were created to script to load the objects are listed. When I compared the directories I see the problem right away. The BCP file that is used to move the data does not have a fully qualified name. When the Southwind and Northwind copies take place at the same time there must be some file contention taking place. It is this file contention that results in only one of the databases having the tables populated while the other remains empty.

It seemed like an obvious solution once I figured out what was causing the problem but tracking it down took quite a while. It also shows why doing some digging into the options of a task can yield some great dividends.

One other caveat to my situation was that the customers table for one of the databases contained millions of rows. In my opinion this made the likely hood of the collision much greater because of the time it took to load the tables. In an environment with small tables this issue might not be noticed. In a future article I will discuss fill factors that did not propagate when I used the SQL Copy objects task. I am still trying to figure out the solution to that mystery.

Total article views: 8680 | Views in the last 30 days: 5
 
Related Articles
FORUM

Database objects deployment

Database objects deployment

FORUM

Transfer SQL Server Objects Task

Transfer SQL Server Objects Task

ARTICLE

Migrating Database Objects

Not a fun task, although SQL Server 2000 makes the job easier than a few other platforms. Still it's...

BLOG

SQL Server – Hide system objects in Object Explorer – SQL Server Management Studio

By default, SQL Server system objects are listed in Object Explorer in Management Studio. These syst...

FORUM

How to create SSIS package in SQL Server Database System

How to create SSIS package in SQL Server Database System

Tags
dts    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones