Problem with "Transfer SQL Server Objects Task"

Kevin Otte567895


Hi everyone,

I'm currently trying to copy a database from one server to another (both SQL2005) using Business Intelligence Development Studio.
I've created an SSIS package. The following parameters are defined:

DropObjectsFirst true
IncludeExtendedProperties true
CopyData true
ExistingData Replace
CopySchema false
UseCollation false
IncludeDependentObjects true
CopyPrimaryKeys true
CopyForeignKeys true

The package fails with the following error:
Violation of PRIMARY KEY constraint 'PK_tblCallStatus' Cannot insert duplicate key in object 'dbo.tblCallStatus'

I know what that error means but I don't understand why I get it.
Isn't the package supposed to completely overwrite the destination database
It obviously does not. When I manually delete all records from 'tblCallStatus' in the destination database it works fine. I can't remember I had to do that in a SQL2000 environment using DTS.

Hope anyone can help since this is almost driving me nuts ;-)

Thanks in advance,
Kevin

michal


I've had the same problems, and for what it's worth, here's what I found:

setting copySchema to true fixed a few problems

CopyForeignKeys.....it doesn't work, so don't do it. SSIS seems to try and create foreign keys before it has created all the primary keys, so it can sometimes fall over

check jamie thomson's article for possible workarounds of the foreign key problem:
http://blogs.conchango.com/jamiethomson/archive/2006/02/17/SSIS_3A00_-How-to-load-related-tables.aspx

do you have service pack 2 installed

michal

jarod1701

Hi Michael,

I've also experimented with 'copySchema'. No luck so far.
Service pack 2 is installed on both servers.
I've read the article you're referring to and I just can't believe that the "Transfer... Task" isn't able to perform such a simple thing.

Kevin Otte

Oops, sorry for the chaos.
The previous post was also made by me. I currently have two Passport identities.
I'll try to not use the other one anymore...

*edit*
This is starting to become really weird. I've manually created a database (no tables, sps etc.) on the destination server. When I tell the "transfer... task" to copy the production database from the source server to this new database it gives me the following error:

"Cannot find the object "dbo.tblTasks" because it does not exist or you do not have permissions."

Of course the table doesn't exist. The package is supposed to create it. A lack of permissions can't be the problem as well since I'm sysadmin.

I'm really stuck here. SQL 2005 has been out for quite a while now. I just can't believe that this is a bug.

michal

Hi Kevin

"Cannot find the object "dbo.tblTasks" because it does not exist or you do not have permissions."

That means that you have set 'dropObjectsFirst' to true. It's trying to drop a table that doesn't exist. Your best bet is to either drop the tables before hand and set 'dropObjectsFirst' to false, or make sure the objects exist on the target DB.

although you have SP2 on both servers, are you creating the package on one of those servers Or do you have SP2 on the machine that you are creating the package

Everything I've read about this suggests using backup/copy/restore, which isn't brilliant.

The only thing I can really suggest is stripping the package right down to simply copying a few tables, then rerunning it over and over adding more and more options until it falls over. Trial and error I know, but at least you will know the SSIS limitations

michal