SSIS Transfer Database Task – Pretty Useless

Today I had the task of migrating the SQL Server 2005 databases from Development to the UAT Servers. This, I thought, was a job for SSIS, seeing as there is the handy Transfer Database Task which promises to move an entire database from server to server in one simple task.

Or, perhaps not. At first I suffered from usual security issues, with roles conflicting etc… nothing unusual, certainly nothing to blame on SQL Server or SSIS and par for the course.

Then the fun started. First, I can’t move one of my databases because SSIS helpfully tells me:

Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of “output column “ErrorCode” (34)” and “output column “ErrorCode” (14)”.  helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}). The Execute method must succeed, and indicate the result using an “out” parameter.

This is normally a package error becuase you have multiple outputs of the same name. But a Transfer Database task has no outputs. The only reason I can think of is that one of my tables has a column called “ErrorCode” – for trapping SSIS Error outputs. I need to check this, but i’m pretty sure.

Second, and far more cripplingly, I discovered a bit of a fatal flaw in the task. It uses (or vice versa) SQL Server’s database scripting to create the database schema. (Right click on the database > All Tasks > Generate Scripts). Which in a stroke of boneheaded programming doesn’t generate the Tables before the Views! So if you have a view which depends on a table within the database, if it gets generated first by the script, it fails validation on the SQL side, which then causes the task to fail. I can be fairly confident that there is no internal checking for dependencies on the views either to determine what order they should be generated in. This means you cannot rely on this task in a production environment, as even if it works first time round, any change in the schema can ruin it.

At which point I gave up using SSIS, reverted to using SQL Server scripting, and manually editing the scripts myself to get them to work.

The Transfer Database Task: A great idea, so poorly implemented as to be useless.

Read More