So, as most people who would be reading this blog know, I am a pretty big fan of SSIS. There are, however, some serious limitations to the product that still exist. One of these deficiencies came back to bite my team over the weekend.
Essentially the problem we were experiencing over the weekend comes down to this: The sql object transfer task appears to be schema unaware… to a point.
For instance, if you have two tables with the same name but in different schemas, dbo.Employee and Dim.Employee, you will be able to select one individually from the initial list. But, for some reason, the task sees all tables as belonging to whatever schema is the default schema of the current connection. So, if you then open the task again, both tables will be selected. More than that, now, when you run this task, a truncate table statement will be sent – twice – against the default schema version of the table, followed by a bulk insert – twice – against the same table. Even if you wanted to load each of these tables it would not be possible. But, worse than that, you have now doubled the load on the first table; and potentially caused a primary key violation which would probably bring the rest of your processing to a screeching halt. (NOTE: it appears that the tables ARE copied correctly if you tell the task to copy all tables).
One of the fun parts of this bug was that the second version of the table which caused us issues wasn’t even in production. The task had simply populated the table list with the test version such that we were receiving the duplicate load and subsequent primary key constraint errors.
Anywho, here are a few topics from the forums that cover this issue:
And here, after some searching, is at least one of the connect issues, which, as humor has it, says that this has been fixed. Perhaps in SP3? It isn’t listed in the release notes:
As I had not yet seen this note while at work I decided to give this a go (after having written most of this post). After having tested this out using SP3, the item at first appears to be resolved – but it now forces a bug the other way. From what I can see of SP3, regardless of which table you check, all tables that have the same name will be transferred regardless of schema. Frustrations abound even more. Is this such a difficult task to program?
Anywho, I saw this post while researching the problem earlier today, which might help as a potential workaround for some users.
Connect Rant and Other Issues
One of the worst parts of the experience for me was that when I went on the connect website to search for this issue I had to deal with page load errors, poor page design, and last but not least, no ability whatsoever to actually submit anything. I was counseled to click the “Submit Feedback” button, but it appeared to be more like a submit feedback image. There was no click-ability and no workaround that I could see. Pure frustration.
Here are a few leftovers from going to the connect site to try to input a new bug, since I couldn’t find one relating to the topic above. These are things I have voted on in the past that I would hope to have fixed or reconsidered going forward.