Good evening,
I'm working with two sql2005 databases on the same server. I would like
to copy the stored procs, views, and udfs from one to another, dropping any
duplicates on the destination side. To do this, I created an Integration
Services Transfer Objects Task in Visual Studio 2005, set what seemed to be
the suitable properties, and let it go.
The first time, it failed with a "foreign key not applied"-type error
(not the exact phrase). I modified the settings so that CopySchema was false
and CopyAllSchemas was true, and tried again. This lets the task run, with a
looooong pause (~9 minutes) between beginning and ending, but fails to modify
anything in the destination db.
This is strange. Anyone know what is going on? TIA.

--Noel

Hello Noel,

What was the "foreign key not applied"-type error exactly ?

Have you run profiler at the same time to see what is happening?

Allan

Thanks for your reply, Allan.

"Allan Mitchell" wrote:

"[Transfer SQL Server Objects Task] Error: Execution failed with the
following error: "ERROR : errorCode=0
description='FK_empMain_empHRInfo' is not a
constraint. helpFile= helpContext=0
idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}"

Which profiler? I'm not too familiar with the new environment in either
VS2005 or SQLSMS.

Hello Noel,


SQL Server Profiler it is the Performance tools directory in your SQL Server
Program files directory


It will tell you what the task is trying to do.

Looks initially as though what is maybe happening is that the task is trying
to alter a constraint that has not yet been applied ('FK_empMain_empHRInfo')

Allan

Thanks. I will check out exactly what is happening.

I am still confused by this error, though. The lack of documentation as
to what the SSIS Copy Object settings for schema copying in the
included Help files is appalling! The task should not be attempting to
alter any constraints. All that it should be doing is copy a set of
UDFs, SPs, and Views from one place to another.

This exact same thing was easily done, with no errors, using SQL
Management Studio Copy Objects wizard.

Finally, there's no excuse for taking > 9 minutes to fail off of a
schema-related error. I'm not impressed with this DTS replacement at
all. Microsoft should be falling over themselves to release an update
for SSIS and the related docs.