Transfer SQL Server Objects Task internals

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.

SQL Server 2008 CTP6 gives you PowerShell

The sixth CTP of SQL Server 2008, made available this past week at http://connect.microsoft.com/sql provides a PowerShell provider for SQL Server, which is backwards compatible with SQL Server 2005 as well. I showed it a little at the User Group in Melbourne a few days ago, and some people seemed to like it.

powershell_sql Basically, you can now open up PowerShell, and change directory to the PowerShell drive “SQL:”. Then change directories through the instances, databases, tables, and so on. At any point, try something like “dir | gm” (gm is Get-Member, dir is an alias for Get-ChildItem), to find out what properties and methods are available on the objects in that folder.

Whilst T-SQL will probably remain the preferred environment for many, this scripting seems to be a step up from using SMO through PowerShell. However, it seems to be just a wrapper for SMO – when I tried to find out information about the Script() method on tables, it turns out to take a parameter of type Microsoft.SqlServer.Management.Smo.ScriptingOptions – I found this a little disappointing, and I haven’t figured out the best way of getting help on methods that are on these objects either.

It’s definitely a good start though – should be a very nice feature of SQL Server 2008.