May 28, 2013 at 5:37 pm
Excel source file contains 12,483 rows. For the SSIS Unpivot transform I utilize Excel Source transform to pull these in. Rowcount after unpivot transform = 50, 236 rows.
For the T-SQL unpivot I first import the excel file into a SQL table. It contains 12,483 rows. Rowcount after T-SQL unpivot = 46, 577 rows
The pivot key value is Month number (1 through 12) and the Destination column is Amount. Pivot key value column name is Month. Same for T-SQL.
All things equal, why would unpivot transform output more rows? Where to begin troubleshooting?
May 29, 2013 at 12:52 am
Where to begin troubleshooting?
Put both result sets in database tables and run some queries so that you can understand what the extra rows look like. Then, maybe, you can begin to understand why there are differences.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 29, 2013 at 8:19 am
good idea.
so far I had only made a subset of the source data (10 rows) and replicated the rowcount problem (20 output with unpivot transform and something like 17 with tsql transform).
OK, hop to evaluate the end results and THINK;-).
May 29, 2013 at 1:35 pm
I found the source of the problem.
When the UNPIVOT transform runs into a row, where every column which is being unpivoted contains a null value, rather than ignore it, it brings it forward in its' original state.
T-sql on the other hand T-SQL Unpivot doesn't bring those records forward into the unpivoted data set.
So, for example, I have Month 1 through 12 columns which contain amounts. I have unpivoted/aggregated each month's amounts into one Amount column and created one Month column to identify the month. Well, if each of Month 1 through 12 contains a NULL instead of an amount then the above behaviors occur ie. transform brings them forward and t-sql does not. This makes the rowcount larger for the transform.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply