How often do you wonder what something’s actually doing under the hood (in the way that it talks to SQL Server)? It’s a trick I’ve written about before – when working out how SSRS did multivalue parameters. This time, it’s Integration Services’ turn.
SSIS has this Transfer SQL Server Object task, designed to be able to transfer objects like tables from one database to another. But Ali got in touch saying that it wasn’t transferring the default constraints for his table – that thing which means that the current date, 0 or ‘dbo’ gets populated in the field when you don’t give it another value to use.
I’ve never had much cause to try out this task, so I started playing. Unfortunately, I’m still not that keen. I’m looking at the SQL 2005 version, not the 2008. Hopefully it’s better – if I have the time I’ll try it and post about it later.
First I pulled up the Edit window, to see what I could do. I went straight to the DefaultsList property, to see what I could move across explicitly. Nothing in there for the AdventureWorks database. Odd – sys.objects tells me there are default constraints. Perhaps you need to have the parent object selected for transfer first – still no luck. SQL Profiler to the rescue, it turns out that the query that SSIS is using to list the Defaults is:
SELECT
obj.name AS [Name],
SCHEMA_NAME(obj.schema_id) AS [Schema]
FROM
sys.objects AS obj
WHERE
(obj.type=N’D’ and not(obj.parent_object_id > 0))
ORDER BY
[Schema] ASC,[Name] ASC
It’s looking in the right place for the defaults, but the thing that’s upsetting is that it’s not listing the defaults that are created as part of a column definition. To make a default which is listed here, you must create the default first and then bind it to a table’s column afterwards – not the way that most people do their default constraints.
I could reproduce Ali’s problem easily enough – when I created a table with a default, and then transferred it from one database to another, the default constraint was missing. I think it must call the SMO Script() method without the ScriptingOptions set – when I did this (using PowerShell) I found that without parameters, the script didn’t include default constraints.
What I found even more frustrating is that when I tried to transfer an object that wasn’t in the default schema (I tried the Sales.Store table), SSIS simply couldn’t find it, and gave me an error. Profiler showed that sure enough, it was only for objects in the default schema, despite the fact that I had selected the object from SSIS’ list in the first place! Go figure. I do like SSIS, but I think this is one task that may be worth avoiding. Hopefully they’ll fix it up later.
You can easily roll your own of course… by querying the system views yourself, like I did for indexes a while back. Use FOR XML PATH(”) to do your string concatenation.
