BIDS is the acronym we use around the office for Microsoft’s SQL Server Business Intelligence Development Studio.  It’s an invaluable tool for moving data around between, into and out of SQL Server.  It’s extremely powerful and was a major improvement over the old Data Transformation Services bundled with SQL Server 2000.  The problem is, I don’t think it was ever tested by real life users.

Let’s just start with the interface.  The GUI in BIDS is pretty typical of modern Microsoft apps whereby there are multiple floating panels that you can dock anywhere you please.  They’re ok, I only have issues with the pinning when it refuses to auto-hide.  My screen isn’t big enough to have everything open but I would imagine that on a 24″ widescreen pushing 1900+ pixels wide, it would be very functional with most panels always open.  The real problem is in the Control Flow panel.  If you’ve got one lonely little task in that panel, BIDS, for some reason, can’t detect where that task is and have it auto-centered.  Most of the time I’ll open a package and have to scroll around the screen looking for the one task.  But this problem is merely annoying compared to some of the other problems.

One major issue I have is that when you first open a project, BIDS tries to validate every single package in the project.  This can take a very long time, especially if some of the packages rely on connections that aren’t live offline or rely on attached devices.  Those packages will fail validation, pop errors, and waste time – since you may not have been working on those packages anyway.

Another issue I have is with connections in the Connection Manager.  If you rename a connection, and that connection is used in a nested task, chances are pretty good that you’ve now broken that nested task.  BIDS, like most Microsoft products, uses an internal unique ID instead of the names (likely in the name of performance) to actually point to connections.  Unfortunately it doesn’t seem to recurse into all of the tasks to update the ID/names of assigned connections if you change a name – causing hours of frustration as you hunt down why a package that worked before a simple name change won’t work now.

My last complaint, for today, is with the SSIS Import and Export Wizard.  This is the only way to graphically pull or push data from and to your servers.  If you choose to copy a table from one server to another, where the table already exists, you have the option of deleting the existing data.  If you choose to delete existing records, BIDS creates an Execute SQL Task that is supposed to have all of the DELETE statements.  It doesn’t.  What you end up with is a set of GO statements.  That’s it.  One for each table you wanted to overwrite.  It took me three or four tries with primary key violations to realize that nothing was being deleted.  This is consistent and cannot be fixed.  I end up having to do the deletes on my own.  Obviously that’s not a big deal but it’s such a simple bug – did no one try to do one of these in testing?

Ugh.  Any other strange BIDS problems out there?