No Object Copy Option in SQL 2005 DTS
-
The option to "copy objects and data between SQL Server databases" is now gone in SQL 2005 DTS. You only have the option to copy tables or write a script. Also, the checkboxes to include all dependent objects, security options, table options, etc are also gone and the "use quoted identifiers" box is now unchecked by default. I guess we just have to script objects now instead of using DTS to copy them between servers.
Has anyone heard whether these options will be re-added in sp1? Has anyone heard complaining about this feature being taken away? Thanks.
-
This was removed by the editor as SPAM
-
Lets start by getting terminology organized so we understand each other. Data Transform Services (DTS) does not exist in SQL Server 2005. This feature has been replaced with a new service with its own engine, SQL Server 2005 Integration Services (SSIS). From the SQL Server 2005 Management Studio (SSMS) only the Data/Table Import/Export, and Copy Database wizards of SSIS are available. To access the tools to "copy objects and data between SQL Server databases", you will need to use SQL Server 2005 Business Intelligence Development Studio (SSBID or BID) or any more robust version of Visual Studio.Net 2005 to create a SSIS Project. Within the Project you can create a SSIS Package by hand or with the wizards provided. The specific tool you are looking for is now called "Transfer SQL Server Objects Task". In its properties you specify everything you would in the SQL Server 2000 DTS Copy Objects Wizard.
I hope that all helps.
Firstmalone
-
Thanks for your reply. This definitely sets me in the right direction. I was able to find the "Transfer SQL Server Objects Task" but I was unable to see how individual objects can be selected for copying. It seems that it is all or nothing if you manually edit the properties of this task. You mentioned that there is a wizard provided for this but I was unable to find that. I'll continue to poke around but if you have found a wizard that resembles the old SQL 2000 DTS Export Wizard please let us know. Thanks!
Ryan
-
For the life of me I still have not been able to get the "Transfer SQL Objects Task" to work. I created a project to copy one stored procedure from Adventureworks to Adventureworks2. I right-click the project and select Execute Package and it appears to complete with no errors. However when I check to see whether the SP has, in fact, been transferred to the Adventureworks2 db it has not been. Perhaps this is just running the package in debug mode. Sorry but I'm totally new to VS and I would really appreciate any pointer you all might have. Thanks!
-
Hi Ryan,
I have had several problems with this task myself including the one you mentioned. To fix that problem (and likely move on to the next), you'll need to set the "CopySchema" property to True.
I have been having some difficulty transferring Stored Procedures with this task and am just about ready to give up on trying to make it work and just roll my own. I hope you have better luck with this task than I have.
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.
This is a problem because if you set the "DropObjectsFirst" property to True and the procedure does not exist on the destination database, it will throw an error because the object it's trying to drop doesn't exist and so it aborts the object transfer. If you set it to False and the object exists at the destination database, it throws an error because it already exists at the destination DB.
I wrote my own task to drop all the stored procedures on the destination database that exist on the source database but I can only get about 10 objects copied before it errors out telling me the object already exists at the destination database. I even tried deleting ALL procedures at the destination DB and still get the same result.
In the task author's defense, I will say that I was able to get it to work consistently on 1 of 3 different databases, on the other 2 DB's, it consistently fails. Good luck, I hope the tip helps you out.
-
Steve,
Thanks for the reply. We are currently running SP1 and I wonder if any of these issues have been fixed in SP2?
-
Hi Ryan,
I was hoping that too, so I updated to SP2 a few days ago. No change in behavoir. I just ran a trace on the DB and as expected, didn't glean any useful info from the Profiler. The problem is, the task is a "black box" to me and how it goes about doing what it does internally isn't readily obvious (or exposed to the outside world). All I can find out is that it failed - not too useful since I can tell that by the Red color and error message!!
SP2 did not solve the problem for me. The properties and settings are fairly straightforward so I'm having a hard time believing these problems are anything but bugs (at the very least poor error handling in the task). Good luck with this task.
-
I have to copy tables from SQL2K not owned by (default) dbo role to SQL2K5 using Transfer Object Task in SSIS. But (may be) as tables were not owned by 'dbo' it returns an error - " [Transfer SQL Server Objects Task] Error: Table "F20111" does not exist at the source. "
Can anyone suggest a solution ??
Alexander
-
Ryan, I don't know if you've solved your problem or not but here's some information that you might find useful. As it was stated in a previous post, 2005 has no "DTS" per se... but it is still the DTS engine driving a lot of the SSIS functionality. If you want to do a similar objects copy like in 2000, open your Object Explorer in 2005 and right-click at the database level. You are presented with an option to 'Import Data..'. There's the method you can use for the objects transfer. It is similar to the old DTS option but slightly different. Works like a charm... I just used it to copy a table from the 2000 server to 2005.
-
William,
That's weird. Like everyone else in this thread, except you, the Import/Export Wizard that I see in SSMS has no option for copying objects. I only see options for copying data and writing a query to copy data.
Greg
Greg
-
Thanks for your response. As far as I can see, the SQL Server Import and Export Wizard only allows you to copy tables. Other objects such as views and SPs are not listed as they were in the old DTS.
-
I have to copy tables from SQL2K not owned by (default) dbo role to SQL2K5 using Transfer Object Task in SSIS. But (may be) as tables were not owned by 'dbo' it returns an error - " [Transfer SQL Server Objects Task] Error: Table "F20111" does not exist at the source. "
Can anyone suggest a solution ??
Alexander
--- you need to map the account you use in the Source Connection Manager to the dbo user because SSIS trys to poll the Source server with the following
SELECT
stbl.name AS [Schema],
tbl.name AS [Name]
FROM
dbo.sysobjects AS tbl
INNER JOIN sysusers AS stbl ON stbl.uid = tbl.uid
WHERE
((tbl.type='U' or tbl.type='S') and(tbl.name=<<table name>>
and stbl.name=N'<<user name specificed in connection manager')
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply