November 27, 2006 at 6:39 pm
Has anyone else encountered the problem with the Transfer SQL Server Objects task? When you select the drop destination objects first, you will get an error if the objects don't exist. This is a serious production issue. It could be resolved with code in the package but it seems to me to be a serious oversight by Microsoft. This should be a very common scenario that would hinder operations dramatically for busy DBAs/DB programmers.
Any non-kludgy solutions to this? Right now the only convenient way to do this without code that I am aware of is to manually drop objects or generate drop scripts and run that code prior to the Transfer SQL Server Objects task. Any other way involves writing code.
Karen Gayda
MCP, MCSD, MCDBA
gaydaware.com
November 30, 2006 at 8:00 am
This was removed by the editor as SPAM
March 14, 2007 at 10:18 am
I have the same problem. I also have a problem in the fact that even though I tell the stupid task to transfer the stored procedures it fails to do it. As far as I can tell the Transfer Object Task is completely broken and useless.
March 21, 2007 at 8:51 am
Ahh, apparently Service Pack 2 fixes many of the problems in the tool.
March 22, 2007 at 5:43 pm
It seems to work if the "CopySchema" is set to true for the destination after SP 2
Karen Gayda
MCP, MCSD, MCDBA
gaydaware.com
March 23, 2007 at 4:54 pm
Not for me across servers. I still get the error:
[Transfer SQL Server Objects Task] Error: Table "ACHInvestDetails" does not exist at the source.
October 30, 2007 at 2:02 pm
It appears that my hesitation to switch all of my databases to SQL 2005 was well-founded. I have just switched my first one, and the Transfer Objects function is a complete and utter joke. I know I shouldn't be surprised that such a basic, but important function, would get lost in the shuffle when MS updated SQL server. I still can't believe that they botched this up, and how much friggin' time I've wasted trying to do something that used to be SO simply in SQL 2000 and only take me minutes to do.
November 1, 2007 at 5:59 pm
View this article for detail on the subject http://www.sqlservercentral.com/articles/SQL+Server+2005+-+SSIS/2955/
Karen Gayda
MCP, MCSD, MCDBA
gaydaware.com
July 17, 2008 at 2:18 pm
This is a good article.
I, too, have had a variety of problems with the Transfer Objects task. The one I am currently fighting is that it wasn't able to transfer data from a source to a destination when the source was SQL 2000. Error went away when the source was SQL 2005.
I have also run into similar problems trying to copy a database with many objects, although more often then not, I can get it to work, there are just some circumstances where it breaks down.
Other issues I have run into involve defaults assigned to columns not transferring even though every possible flag can be set in SSIS transfer objects task, although I was able to convert this to SMO and make it work, so the libraries do support that functionality. It led to a shortcoming in SMO, but I could work around that too.
I think the key word to working with SSIS in SQL 2005 is "workaround". Eventually you will find some kludge to get done what you initially wanted to do, but the shortest distance between two points is often not a straight line with this product----hoping MS gets it right in SQL 2008.
September 19, 2008 at 2:30 pm
I too have found serious, dangerous issues with SSIS and the Transfer SQL Server objects task.
First, I found that SSIS is trying to delete logins:
IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = N'APPLEAD')
EXEC master.dbo.sp_droplogin @loginame = N'APPLEAD'
Why is the SSIS package trying to delete logins?
FIX: The logins are incorrectly deleted when you run a SQL Server 2005 Integration Services package that contains a Transfer SQL Server Objects task (http://support.microsoft.com/kb/945323)
To work around this problem, set the value of the DropObjectsFirst property to "False" for the task.
Then I find that SSIS is trying to add logins even though I specified CopySQLServerLogins=False
EXEC master.dbo.sp_addlogin @loginame = N'TESTDTA', @passwd = @randomPwd, @defdb = N'master',
@deflanguage = N'us_english'
I still have yet to figure that one out.
I am shelving SSIS until Microsoft gets it figured out, it is just too dangerous.
November 1, 2008 at 6:54 am
I am trying to use the Transfer Login Task in SSIS to create logins on our new dev server from our old dev server. Both servers are SQL 2005 SP2. When I try to run the package I get the following error.
Execution failed with the following error: "Script transfer failed. ".
Error: 0xC002F325 at Transfer Logins Task, Transfer Logins Task: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
Any logins I had on the new dev server are gone except for sa.
Any suggestions?
December 9, 2008 at 9:17 am
This worked for me too. Thanks. But seriously folks, what in the world were they thinking? How would you ever guess that the Copy Schema option enabled the rest of the package to run?
[font="Comic Sans MS"]Tom Powell
http://philergia.wordpress.com/[/font]
December 16, 2008 at 7:28 am
Hi,
I am struggling trying using Transfer SQL Objects Task Editor to transfer table between 2 databases.
I just want to copies somes tables (with indexes) own by DBO. If I select “CopySchema” it drop the user and recreate them without assigning the roles (not good). If I set “CopySchema” to false, the destination are not cleaned before, so I get all my rows in double in my destination database (even if I set the “dropObjectFirst” to true.
Also, having trouble transferring function & stored procedures.
How I can set that properly option of “Transfer SQL Objects Task Editor “ to transfert functions, SP & the tables with indexes without problems?
Rem
April 23, 2009 at 2:05 am
Hi there
I have the same problem now with SQL Server 2008 SP1!!! any one else:w00t:
October 5, 2009 at 9:50 am
Hi,
the working solution for me is to set "CopySchema":true.
But in the next step I have to set "DropObjectsFirst":false, due to some bindings (functions in computed columns) didn't worked.
Have a nice day, Egon
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply