UNPIVOT behaves differently in 2005 and 2010/2012

  • I am in the process of migrating some gnarly old SSIS packages from SQL Server 2005 to SQL Server 2012. The only piece that is behaving discrepantly is an UNPIVOT step in a Data Flow task (text flat file source).

    In 2005, it is outputting blank values as blank values.

    In 2012, it is outputting blank values as NULLs.

    Both servers and packages are set up identically.

    I'm just wondering if this is a known issue, or if any of you have experienced this?


    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • autoexcrement (6/5/2015)


    I am in the process of migrating some gnarly old SSIS packages from SQL Server 2005 to SQL Server 2012. The only piece that is behaving discrepantly is an UNPIVOT step in a Data Flow task (text flat file source).

    In 2005, it is outputting blank values as blank values.

    In 2012, it is outputting blank values as NULLs.

    Both servers and packages are set up identically.

    I'm just wondering if this is a known issue, or if any of you have experienced this?

    Can you post the code where that problem is occurring? I'd like to do a couple of experiments in the near future.

    As a bit of a sidebar, I've never liked the comparative mess they made with PIVOT/UNPIVOT and have stuck to old methods that seem more reliable and have proven to be as much as twice as fast when pre-aggregation was possible. If you've ever studied the PIVOT function in MS Access, you'll still that the SQL Version is a poor and very underpowered excuse for what PIVOT should be.

    p.s. Because I have stuck with the older methods, I can't say if it's an issue or not because I've not used the newer methods except for testing purposes and then just one time. My recollection for numeric PIVOTs was that they returned NULLs instead of zeros in 2005, which is part of the reason I didn't care for PIVOTs to begin with. I know that's not the same but thought I'd bring it up.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply