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.