August 20, 2007 at 10:22 am
Hello All,
Does anyone know what has changed under hood from the DTS “Copy SQL Server Objects Task” to the SSIS “Transfer SQL Server Objects Task”?
I’m specifying that I want 3 tables copied from one server to another and to have the data to be replaced. There are foreign key constraints between each table. I’ve set up all the properties in SSIS to be the same as DTS, eg, in the Table Options I set them all to True, including "Copy Foreign Keys".
When I ran the job, it failed. When I view the Job History in the Management Studio, it is putting out warning errors that the tables “already exists at destination”.
Unfortunately, when viewing the Job History there is only 1024 bytes that are trapped for the "Message". Therefore if there were real errors, I cannot see them because they would have been cut-off.
I have been reading through the SSIS forum and found statements like:
“I can't really help with your issues, but I will second the report that "Transfer SQL Server Objects Task" has a lot of problems.” (RE: Transfer SQL Server Objetcs Task ERROR
Posted 6/26/2007 2:38:00 PM)
“One of the inherent flaws in this task is that it aborts copying objects as soon as it has an error, regardless of how many errors you tell it to ignore through the "MaximumErrorCount" property.“ (RE: No Object Copy Option in SQL 2005 DTS Posted 3/15/2007 8:03:00 AM)
So this is pretty discouraging, when we can’t see what is happening under the box and error messages get truncated.
Does anyone know of a forum that the SSIS Microsoft authors moderate?
....Celeste
August 21, 2007 at 7:31 am
Unfortunately, welcome to the club. I found in a blog by a Microsoftie that said MS won't be doing anything with the this in the 2005 edition but will "probably" addresss it in 2008. Look at this link for some help:
http://blogs.msdn.com/mattm/archive/2007/04/18/roll-your-own-transfer-sql-server-objects-task.aspx
There is also an excellent article on this site that explains how someone went about creating his own workaround.
http://www.sqlservercentral.com/columnists/sLaPlante/2955.asp
I found it too complexfor my needs. Most of the time, I just need to copy a table or two with data to a test system.
hope this helps
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
August 21, 2007 at 2:01 pm
Hi MG,
Thank you so much for providing those two links along with a summary explanation.
This is so frustrating, because it worked in SQL 2000 DTS. Now with SQL 2005 SSIS, we are forced to learn and use a more complicated envirionment for which after that effort, this particular task doesn't even work! What a loss of productivity on many programmer's part!
...Céleste
August 21, 2007 at 2:08 pm
I know very well how frustrating is. I spent a few days trying to fix what I was believed was wrong. I thought the time would be well spent since the DBAs in the group would need to use the task also.
Good luck with whichever direction you take.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply