Transfer SQL Server Objects Task throws an error because the DropObjectsFirst property does not
I wanted to create a package to copy the objects from one database to another and replace those object if they already exist. Therefore, before the package executes you do not know whether all the objects exist on the target server or only some of them.
Using the 'Transfer SQL Server Objects Task' I have found that I cannot get this to execute cleanly by itself. If I set the 'DropObjectsFirst' to false then an error is thrown if the object exists and if I set it to true then an error is thrown if it does not exist.
In order to get round this I have had to create an 'Execute SQL Task' to list all the objects and then go through them dropping them on the target server in a for each loop before executing the 'Transfer SQL Server Objects Task' with 'Transfer SQL Server Objects Task' set to false.
However, is there a better way of achieving this or am I missing something in the 'Transfer SQL Server Objects Task'?
 RickHalliday wrote: |
| If I set the 'DropObjectsFirst' to false then an error is thrown if the object exists. |
|
Since you have set "DropObjectsFirst" to false, the object should not exist in the destination. Please make sure that it does not exist at the destination.| RickHalliday wrote: |
| If I set it to true then an error is thrown if it does not exist |
|
This behavior is due to a bug in the current version. The fix will hopefully made available soon in a future release.
We are having this problem too which basically means the task is useless if you want to copy just a few objects instead of the whole db and there is an error along the way. The maximumerrorcount property for this task also seems to have no effect on continuing execution. This really needs to be fixed.
The bug I mentioned in my previous post was related to "DropObjectsFirst" property. If you do not set it to true, then you shouldn't have any problems in transferring objects across SQL Server 2005 databases. So, you should be able to transfer selected objects from one SQL 2005 db to another.
There was a bug related to using SQL Server authentication at the transfer destination which resulted in an error message "Cannot apply value null to property Login". It has been fixed in SP1. The work around before SP1 was to use Windows authentication. Now, you can get SP1 bits to get the fix.
There is another bug related to transferring objects from a SQL Server 2000 db to a SQL Server 2005 db. See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=331469&SiteID=1 for details.
Hope this helps. If your scenario is not covered in the scenarios mentioned above, please post it and we will confirm if it is a bug and if it has been resolved.
Is there a typo somewhere between these two statements, they seem to say the opposite?
"If I set the 'DropObjectsFirst' to false then an error is thrown if the object exists" - This behavior is due to a bug in the current version. The fix will hopefully made available soon in a future release.
The bug I mentioned in my previous post was related to "DropObjectsFirst" property. If you do not set it to true, then you shouldn't have any problems in transferring objects across SQL Server 2005 databases
My basic problem is that I need to be able to copy over all tables with constraints and keys to a destination db. If someone creates an index or a new column on the source db I need it to be copied over. In order for this to happen I have to specify copyschema but this fails with the dropobjectsfirst property set to either true or false with "object does not exist" or "object already exists" respectively. This is with 2005 on both servers.
I quoted Rick in my post and probably that caused the confusion. I edited my post above to avoid confusion.
I will try to repro your problem and post my comments later today.
You cannot transfer only indexes or selected columns of a table from one db to the other using Transfer Objects task. You have to transfer the entire table.
So for your case, I can only think of the following approach.
1. Make sure that the table is not present in the destination (use Execute SQL Task to drop it if needed)
2. Configure the Transfer task to copy the table with DropObjectFirst=false, CopyData=true, CopySchema=true, CopyIndexes=true (and other Table options if needed)
3. Run the task
Once the fix for the bug related to "DropObjectsFirst" property (as mentioned in a previous post) is made available, you can skip step #1 and set DropObjectFirst=true in step #2. Since the fix is not available as of today you have to go with the approach mentioned above.
Thank you for looking into this. In most cases we are only concerned with the data transfer, not schema, but that bug is proving to be troulblesome in getting some solutions in place. We will probably just wait until the fix to use it in this scenario so hopefully it comes soon.
Ok, this was the situation on April 25th
What about today? is this already solved? I have the same problem...
Greets,
Tom
I'm wondering what known bugs exist with the "Transfer SQL Server Objects Task" that involve logins being dropped. I currently have an issue in which all the logins have been dropped at the server level. This occurred when running an SSIS package to copy a database to a new database instance on the same server. The package failed with "permissions denied" trying to create a stored procedure (presumably the next step after the logins were dropped ... or perhaps a very odd coincidence). It even appears to have dropped 'sa', and I'm not sure how that's possible. We'll be able to restore the master data files and get the server running (fortunately just a development server), but what a mess.
One thing I did have was DropObjectsFirst set to True, but I assumed those were database level objects, not server level objects. I never expected server level logins to be dropped. Did I miss something? Is there a bug here or am I wasy off base?
Thanks ...
