Business Intelligence Development Studio

You are currently browsing articles tagged Business Intelligence Development Studio.

Small disclaimer: in this post “wide” is not referring to the “Wide Tables” feature, but just means a regular table with many columns.

If you’ve ever needed to load a wide table, for instance one that consists of about 250 columns, you may have come to the conclusion that some manipulations in your SSIS package take forever.  Here’s a quick tip how to speed up your package development.

This tip is actually quite generic and applicable to daily Windows use.  It’s just that sometimes you forget about the most simple things because you’re used to a different way of working.  Why make it easy when difficult works fine, right?  Well, maybe not.

Okay, here it goes: don’t forget that you’ve got a keyboard, do not always use the mouse! The Business Intelligence Development Studio offers a very graphical way of designing your data flow (or control flow for that matter) but sometimes it’s really faster to use the keyboard.

An example.  Imagine the Derived Column Transformation.  To avoid typographical errors you want to select a column name from the tree view on the left in the Derived Column Transformation Editor.  And then you discover that the list of column names is not ordered alphabetically, and on top of that, there’s no way to rearrange them!  Good luck finding your column.

Derived Column Transformation Editor

And then you remember that you’ve got a keyboard.  By typing the first letters of the column name, the selected item will jump down to the first match.  Isn’t that a great invention?!  In the screenshot above I typed “pu” and it selected PurchaseOrderNumber, which is about 32 columns down from the top.

This tip not only works in our Derived Column Transformation but in any screen where a similar tree view appears, such as in the “Inputs and Outputs Properties” tab of the Advanced Editor of an OLE DB Source:

Advanced Editor of OLE DB Source

This can be useful if you need to tell the component that you’ve ordered the data on certain columns.  And why on earth would you want to do that, you may ask yourself.  Well, there are certain components that expect the data to be ordered, such as the Merge item.

If you apply this tip whenever you need to locate something in a tree view, you’ll find that your live has become just a bit easier.

And if you’re not satisfied with the way of working as described above, there’s always the XML!  That’s right, just right-click on your package in the Solution Explorer and select View Code.

Context menu on package in Solution Explorer

Then you’ll discover that a .dtsx file is made up completely of XML.  If you spend some time examining the XML code, you’ll probably find what you’re looking for.

As an example let’s say we want to locate our PurchaseOrderNumber from earlier and change its SortKeyPosition property to 1 (which means the data coming in is ordered by PurchaseOrderNumber).  So I open the XML and scroll down a bit.  I recognize a component which I’ve named “OLE_SRC Wide Table”:

<component id=”1″ name=”OLE_SRC Wide Table”

I also recognize my SELECT statement.  Scrolling down a bit further I see the


followed by a bunch of outputColumn items.  I put the cursor at the <outputs> line and hit CTRL-F.  Then I type “pur” and hit ENTER.

Find and Replace window in dtsx XML

The selection jumps to the column that we’re after.  I close the Find and Replace window and scroll to the right to find the sortKeyPosition property and I update its value to 1.

To confirm that this is working I open up the Advanced Editor again and locate our column.  SortKeyPosition is set to 1!

SortKeyPosition has been updated through XML

I hope that with this I showed you that sometimes it’s interesting to think about what you’re actually doing and try to find a way to do it better, and faster.


Tags: , , , ,

Lately I had been getting annoyed by Visual Studio crashing on me while working on ETL packages.  The crashes seemed somehow related to debugging a package because they usually occurred after I clicked the “Package execution completed. Click here to switch to design mode, or select Stop Debugging from the Debug menu.” link.  But I couldn’t really pinpoint why or when exactly they occurred.  Now I’ve got an explanation, and a solution!

How did I get to the solution?  Well, today I decided to click the “Send to Microsoft” button on the infamous crash pop-up window.  And from it I actually got a link to a fix for the issue.  Apparently the issue is not related to Integration Services in particular, it is a much broader Visual Studio shell issue that occurs in Visual Studio 2008 with SP1 when you’ve got a combination of undocking windows and changing window layout.  And that is indeed what I usually do when debugging a package!  When the package stops executing I often execute the following scenario:

  • double-click the title bar on the Output window to undock it
  • enlarge the undocked Output window to almost full-screen so I can have a good look at the errors
  • double-click the title bar to dock the window back to its original place
  • click the “Package execution completed.” link (or hit the Stop Debugging button)

And that’s exactly the second scenario described in this Microsoft Support article because when you stop debugging, the IDE switches the window layout back from debug to design.

I have now installed the hotfix and the issue is gone.  Beware though if you also use WPF, better read through the whole article and comments on the download page first.

Hmm, this may also explain some other unexplicable crashes I’ve seen lately.  Makes you wonder doesn’t it :-)


Tags: , , , , , , ,

© 2008-2018 BI: Beer Intelligence? All Rights Reserved