Silly SQL #1: OLE DB Destination [SSIS]

Surprised!Sometimes we take silly little things we do in our daily life for granted and assume everyone else is aware of them too.  That’s where we’re wrong: as I’ve found out, they aren’t!  Not always anyway.  That brought me to the idea to start this Silly SQL blog series.  Each post will explain one little thing I do or use regularly that makes my life easier.  Here’s the first one!

Last week I noticed a co-worker making a lot of keyboard noise while implementing a Data Flow Transformation in an SSIS ETL package.  When I turned around to have a look at his screen I saw he was working on an OLE DB Destination, nothing wrong with that.  Basically he was hitting the down arrow followed by TAB twice, down arrow again and so on in order to set up matching input columns with destination columns.  This method worked because we decided to put the incoming fields in the same order as the columns in the destination table and we also gave them the same name using aliases in the source query.

However, for tables with over 200 fields this method is quite tiresome (and annoying for colleagues unless they’re using a headset).  Nice as I am I decided to help him out.  I asked him if I could borrow his mouse for a second and then right-clicked in the grey area in between the two tables:

The Map Items by Matching Names functionality

You should have seen his face when he saw that window appear, and even more when I selected Map Items by Matching Names!  Apparently this is some functionality that’s been hidden really well because in that same week I caught another co-worker in exactly the same situation.  And these are not junior profiles I’m talking about!

If you’re now thinking “Hang on, I never have to set up the matches myself?” that may be true!  If you’re always creating new packages and the names are matching then BIDS will set up the matching fields automatically when you open the Mappings page.  But we are working with previously-defined templates to speed up development.  In that case BIDS will not set up the matches so that functionality shown above really comes in handy!

A fast way to find out if all fields have been matched is to click the Input Column header:

Click Input Columns header to put unmatched items on top!

This will order the items with the unmatched ones, recognized by <ignore>, on top!

See, the things you take for granted aren’t always that for others, as proven here.

That’s it for now, let’s see if I can come up with another silly thing for the next post!

Update: this post was turned into a movie by the good folks of Webucator: check it out!

In the meantime: have fun!

Valentino.

Share

Tags: , ,

  1. Tim’s avatar

    HI. Just wanted to inform you that you have a question on http://blog.hoegaerden.be/2013/06/12/formatting-numbers-ssrs/comment-page-1/#comment-16970 that hasn’t been answered that I would very much like to see an answer to as well. It’s about SSRS formatting. It’s the first question on there about formatting multiple rows under the same cell with an expression. I’ve got a similar problem myself, and with MUCH patience and ambition, I’ve managed to make everything a percent… which is close to my goal than previously, but still incorrect.

    I’m sure the guy that asked the original question is LONG DEAD now, but I am still very much alive.

    Reply

    1. Valentino Vranken’s avatar

      Hi Tim,

      Could you post some details on your issue? Please do so under the formatting article :)

      You’ll need to use an IIF condition somehow but it all depends on your particular situation so if you post details on that I’ll try to help you out…

      Valentino.

      Reply

      1. Tim’s avatar

        No problem. I’ll go there now.

        Reply

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