Transfer SQL Server Object Task fixed

It’s nice to give Microsoft feedback and get the product changed!

I first mentioned this in a post last month and logged a connect.microsoft.com entry. Microsoft checked it out, invesigtaed, and have now put a new property into SQL Server 2008 to resolve it! They write:

Thanks a lot for your feedback. We are taking a look at this issue.
Posted by Microsoft on 2/27/2008 at 8:54 AM

Greetings Rob,

First, let me thank you for your feedback on SQL Server; we really appreciate your willingness to spend time and write up the issue as you have, and to send it to us.

As you’ve described, the Defaults query that is run when you expand this property is just looking for defaults you’ve created via CREATE DEFAULT, not the more typical (imo) default constraints on table columns. It does by enumerating the SQL Server Management Object’s (SMO) Database.Defaults collection, which in turn uses SQL as you’ve seen through the profiler. This however does not allow you to choose individual column’s DEFAULT constraints to be copied. In fact, the current implementation of the Transfer Objects Task doesn’t allow many kinds of column constraints to be copied.

For SQL Server 2008, we’ve just added a new property to the task, CopyAllDRIObjects, which corresponds to SMO’s “DriAll” property. This will cause all data referential integrity objects, including constraints, on objects you’ve selected, to be included when copying. So for example if you’ve selected three tables to be copied and they each have columns with DEFAULT (or other types) of constraints, and you enable CopyAllDRIObjects, this will cause their constraints to be copied as well to the destination. DRI objects for objects not selected, i.e. constraints on columns in tables you have not selected, will not be copied.

Thanks again for your post to connect; we look forward to hearing from you in the future. Take care!

SSIS Team

Posted by Microsoft on 3/6/2008 at 5:47 PM

I think the key words here are “on objects you’ve selected” – so that you’re not trying to transfer things you don’t want.

The fact that they took any kind of action is great, and definitely shows that it’s worth jumping onto the connect.microsoft.com/sql site and putting in feedback about SQL Server. If you identify something that should be changed (and it’s easy enough!), then you could have a very quick fix!