Slight SSIS Rant, Or, how I grew to hate the transfer sql server objects task

SQL Transfer

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:

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/74c65a34-eb58-4461-b58f-b603d2eac82a/

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/1fb55287-ab74-47fe-97bb-274b7f29a3b7/

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:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=253793

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.

http://blogs.msdn.com/mattm/archive/2007/04/18/roll-your-own-transfer-sql-server-objects-task.aspx

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.

Expressions Don’t Always Evaluate

Limit on SSIS Expression Length

Base Annotation on Expressions

IsSorted Stored in RawFile

View RawFile Contents  (I have used the excellent Raw File Reader recently to get around this)

Execute From Here

Create an Empty RawFile

Advertisements
This entry was posted in SSIS and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s