RE: SSIS Transfer object failure

Hello Charles,

It works! The problem is the "CopySchema" option it has to be set to
"True". I've downloaded SP2 (CTP) and the behaviour is the same (but faster!)

I am not sure I understand all the implications of this. My noddy logic
says that if the schema exists in both the source and destination then there
should be no need to transfer it. (in my test I was simply using the
defauilt dbo schema). I then thought perhaps you only need to transfer the
schema when transfering the first object it contains - but a little testing
has shown this not to be the case - you still need the CopySchema to be set
true. All of which begs two questions: when would you ever set it to "False"
and why is its default value not "True"?

Perhaps I'm not engaging brain - if you can shed any light then great - if
not no worries. Many, many thanks for your help!

--
Simon


"Charles Wang[MSFT]" wrote:

Hi Simon,
Per my understanding, you were using SSIS Transfer Object Task to transfer
a SQL Server 2005 SP1 database table to another database; though the table
was very simple, the transfer was also failed.
If I have misunderstood, please let me know.

From my research, this issue may be caused by missing selecting some
options. I managed to transfer all the tables and the data of my test
database to another database. My test was based on the following options
which you can check by double clicking your Transfer Object Task:
==Destination========
CopyData:True
ExistingData: Replace
CopySchema: True

==Destination Copy Options=======
CopyAllObjects: False
-- CopyAllTables: True

==Table Options=======
CopyPrimaryKeys: True
CopyForeignKeys: True

Hope this helps. Also, I would like to let you know that though SQL Server
2005 SP1 has fixed many known issues, there are still some new discovered
known issues of SSIS after SP1 released. They will be fixed in SQL Server
2005 SP2. Appreciate your understanding on this.
If you are in a test environment, you may try installing SQL Server 2005
SP2 CTP which is a pre-release of SP2 and has fixed many known issues not
included in SP1.
You can download it from:
SQL Server 2005 Service Pack 2 - Community Technology Preview (CTP)
December 2006
http://www.microsoft.com/downloads/details.aspx?FamilyID=D2DA6579-D49C-4B25-
8F8A-79D14145500D&displaylang=en

Please feel free to let me know if you have any other questions or
concerns.

Have a good day!

Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================


.

Relevant Pages

  • RE: SSIS Transfer object failure
    ... you were using SSIS Transfer Object Task to transfer ... a SQL Server 2005 SP1 database table to another database; ... I would like to let you know that though SQL Server ... 2005 SP1 has fixed many known issues, there are still some new discovered ...
    (microsoft.public.sqlserver.dts)
  • Re: Recover SPS to SQL Server from Desktop Engine data
    ... SP1 it can't happen since the database structure is different. ... But you can try and go to the central administration page of Sharepoint ... Since then I've rebuilt the SBS and installed SP1 as well as SQL Server. ...
    (microsoft.public.sharepoint.windowsservices)
  • Database Creation Errors
    ... I am now having trouble getting my SMS Database to create on the SQL Server. ... I am using Windows Server 2003 SP1, SQL Server 2005, and am in a Active Directory Domain. ... The error message that I get says that the "Database file is compressed but is not a read-only database and the file needs to be uncompressed." ...
    (microsoft.public.exchange.setup)
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Cluster will not fail over.
    ... > As far as the TCP/IP issue goes, you had to rebuild the cluster and were ... > able to restore the master database. ... > a cluster installation you'll have to revisit. ... >> This worked bringing up the sql server in minimal mode. ...
    (microsoft.public.sqlserver.clustering)