September 27, 2006 at 8:15 am
I've found that the new SSIS Import/Export Wizard does not create primary keys on new tables that it creates when copying data from one instance of SQL Server to another. When copying a new table to another SQL Server or db, the wizard creates the new tables at the destination but does not create the primary keys. In the old DTS wizard there was a checkbox to create the primary key at the destination but that option has now been removed in SQL 2005. Now we have to manually script the primary keys at the source and create them at the destination, a very tedious process which opens us up for human error. Is there any way to enable this lost feature?
The old SQL 200 DTS wizard used to be an almost foolproof way to copy tables and data from one place to another but now the process has become more cumbersome with SQL 2005. Thanks for any advice or tips you can offer!
October 2, 2006 at 8:00 am
This was removed by the editor as SPAM
October 2, 2006 at 11:18 pm
SSIS is very different to DTS in many ways that similar names does not quarantee similar functionality.
According to the introductory screen, the wizard is only used to transfer data and not schema. All it does (if you look at the generated package) is the creation of the OLE source and destination objects.
What you want is the 'Transfer SQL Server Objects Task'. This will allow you to transfer both schema and data, with a number of options providing much greater flexibility. It's probably not a bad time to start getting familiar with some of the extensive SSIS components.
October 3, 2006 at 7:02 am
True, we could go into SQL Server Business Intelligence Development Studio or Visual Studio and create a 'Transfer SQL Server Objects Task'. However, this would only be ideal if the data and schemas being copied were the same every time. In our environment we might perform 20 different SQL object copies from development to production everyday, all of which are different. It seems like it would be terribly time consuming to go into Visual Studio every time somebody requests to have new objects moved up to production. Also, the people who perform the work are often pretty low tech.
In the past with SQL 2000, the DTS wizard did all of this work for them and it was fast and reliable. It is frustrating that MS as taken this functionality away and has not offered a better solution. For now, we will need to just manually create (by way of script) any new tables in production before running the data copy wizard. This opens is up for human error but it is a risk we'll need to take until MS offers a low tech method to copy SQL objects, such as was available in the past.
Thanks for your reply. If anyone has any additional suggestions I'd love to hear from you.
October 4, 2006 at 12:06 pm
I have had great results using Red Gate SQL Compare and SQL Data Compare products for this purpose. Definitely would suggest taking a look.
Normal disclaimer. No association with Red Gate other than being a happy customer.
Tim
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply