So I have a C# application (VS 2012 with .NET4.5) that builds SSIS (SQL2012) packages programatically. the packages can be opened in the designer and they run fine. However, there is one case that is giving me a problem. I have an OleDb source connected to a table in SQL server. I am using the unpivot task to convert columns in a sparse matrix to an Entity Attribute Value model. So basically, the primary key value of the source table is a pass-through value in the unpivot task, each column is mapped to the destination column, and the attribute id is hard coded as the pivot key. Like i said this works great EXCEPT i came across one column and a table that was null for all the rows in the table. when I run the package, it fails with: OnError,SERVERNAME,DOMAIN\user,{94E83A3B-5386-4712-BEDC-11E35341675F},{94E83A3B-5386-4712-BEDC-11E35341675F},{3187347C-8D44-4D51-8FDB-B5C4159A58B0},9/14/2012 9:48:02 AM,9/14/2012 9:48:02 AM,-1071607780,0x,There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[AttributeId] on OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was: "The value violated the integrity constraints for the column." So I set up a data viewer on the data flow and found that the unpivot component was generating rows for every null value. not only that, but the values for the key column and the attribute id (which was hard coded) were also null for all the rows sent from the unpivot to the ole db destination. I manually created a package with an unpivot for just the column in question and got the same result. then I inserted a value for every row in the table and the same package runs fine. can someone offer any help or advice on what might be causing this?

The package is failing likely due to a constraint on the AttributeId column that specifies NOT NULL. But that seems a little less odd than the behaviour of the package. AFAIK, it shouldn't be unpivoting NULL rows... at least according to the documentation. Now, perhaps the way you created the unpivot with code is causing a little weirdness... perhaps you could open the package created by code in BIDS, delete the Unpivot, and configure one using BIDS to see how that operates? Talk to me now on

I did that. In fact I created one from scratch in BIDS, pivoting only the column in question and got the exact same result. Ran it twice. once with every row containing nulls and once with '1/1/'2012' as the value. the component generated rows for every null value. And you are exactly right about the AttributeId column. But as you have already figured out, I am more interested in why the rows are being generated for null values in the first place. can you maybe try and reproduce the error and see if you get the same behavior?

How about you take a screenshot of the UnPivot UI - maybe it's misconfigured (or we just don't understand how you've configured it). Talk to me now on

Can you show some input and output (sample) data as well, so I can get an idea of what's coming in and out? Talk to me now on

its just two columns of data that are concerned. ten character numeric strings in the one and null in the other. the pivot key is hard coded in the unpivot component configuration screen. see the output of the data viewer below. How do I get Microsoft involved with this? 2013399057 NULL 2013399488 NULL 2013399770 NULL 2013402244 NULL 2013402440 NULL 2013404066 NULL 2013404070 NULL 2013404203 NULL 2013404206 NULL 2013404401 NULL 2013404589 NULL 2013404705 NULL 2013404738 NULL 2013404768 NULL 2013404784 NULL 2013404813 NULL

its just two columns of data that are concerned. ten character numeric strings in the one and null in the other. the pivot key is hard coded in the unpivot component configuration screen. see the output of the data viewer below. How do I get Microsoft involved with this? 2013399057 NULL 2013399488 NULL 2013399770 NULL 2013402244 NULL 2013402440 NULL 2013404066 NULL 2013404070 NULL 2013404203 NULL 2013404206 NULL 2013404401 NULL 2013404589 NULL 2013404705 NULL 2013404738 NULL 2013404768 NULL 2013404784 NULL 2013404813 NULL

This one shows that after filling in the constant value of '1/1/2012' in the Term_Start_Date column it runs fine. I created this package in BIDS so this has nothing to do with the application that I wrote to build these packages manually.

This one shows that after filling in the constant value of '1/1/2012' in the Term_Start_Date column it runs fine. I created this package in BIDS so this has nothing to do with the application that I wrote to build these packages manually.

I submitted this as a bug to Microsoft. If anyone can reproduce this please go here: https://connect.microsoft.com/SQLServer/feedback/details/763356/ssis-unpivot-component-generates-output-rows-for-null-values-in-the-input and click on the link saying "I can reproduce this too"