July 5, 2012 at 7:41 am
Hi,
Being accustomed to the Powercenter Normalizer, we try to unravel the mysteries of the unpivot component in SSIS
We want to normalize tables containing keys (integer) , numbers (integer) ,values (float) and dates (datetime)
Typical table content (after normalization) would be something like:
Pkey Field_________Value_int ____Value_float ___Value_date
1001 OMG ____________ 20 ______
1001 NUMV ____________ ______ 123.45
1001 LDAT ____________ ______ ______ ______ 2012-7-5
1002 OMG ____________ 50 ______
1002 NUMV ____________ ______ 56.22
1003 LDAT ____________ ______ ______ ______ 2011-3-21
From our first attempts this seems impossible.
Looks like we need 3 normalizer dataflows , respectively writing firstly the records containing integer fields, then the float fields and then the dates to the target .
(With even adding specific null values to write to the target table ??)
July 5, 2012 at 8:09 am
blom0344 (7/5/2012)
Hi,Being accustomed to the Powercenter Normalizer, we try to unravel the mysteries of the unpivot component in SSIS
We want to normalize tables containing keys (integer) , numbers (integer) ,values (float) and dates (datetime)
Typical table content (after normalization) would be something like:
Pkey Field_________Value_int ____Value_float ___Value_date
1001 OMG ____________ 20 ______
1001 NUMV ____________ ______ 123.45
1001 LDAT ____________ ______ ______ ______ 2012-7-5
1002 OMG ____________ 50 ______
1002 NUMV ____________ ______ 56.22
1003 LDAT ____________ ______ ______ ______ 2011-3-21
From our first attempts this seems impossible.
Looks like we need 3 normalizer dataflows , respectively writing firstly the records containing integer fields, then the float fields and then the dates to the target .
(With even adding specific null values to write to the target table ??)
If you were to rewrite your post along the following lines:
I have source data contained in x tables - here is some sample data:
--Sample data here.
I would like to use SSIS to normalise this data so that it looks like this:
--example of normalised data here.
Can you please recommend the best way of achieving this?
You might get a better response.
Also, your final question is, I'm afraid, incomprehensible.
--edit, fix typo.
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
July 5, 2012 at 1:29 pm
Example data stored (in reality 200 fields)
Pkey OMG NUMV LDAT
1001 20 123.45 2012-7-5
1002 50 56.22 2011-3-21
After normalization:
Pkey Field_________Value_int ____Value_float ___Value_date
1001 OMG ____________ 20 ______
1001 NUMV ____________ ______ 123.45
1001 LDAT ____________ ______ ______ ______ 2012-7-5
1002 OMG ____________ 50 ______
1002 NUMV ____________ ______ 56.22
1003 LDAT ____________ ______ ______ ______ 2011-3-21
We observed that SSIS does not work with implicit data conversions, like you cannot union an integer and float value (no problem in T-SQL)
When creating derived columns it is even mandatory to specify the null type. My suspicion therefore would be that it would be necessary to actively write null values to the target table.
Excuses for not being clear enough 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply