locked
Unpivot months question RRS feed

  • Question

  • Hi,

    I have file with following columns:

    Project, Year, Jan_Amt, Feb_Amt,....,Dec_Amt, Sum_Amt (last column can be ignored)

    I want to unpivot it and load to sql table, with following columns:

    Project, Year, Month, Amt

    My destination table has month column as char(2), and months there have to be: 01/02/03/.../09/10/11/12 (zero ahead of 1 digit month). From some reason I cannot make it work:

    I chose following columns as "pass through": Project, and Year.

    I checked input columns Jan_Amt - Dec_Amt as Input Columns, Amt as destination column, and

    I set Month as "Pivot Key Column Name", and Pivot Key Values to: 1,2,3,...,12.

    In Advance Editor, I set Month column data type to DT_STR, with length = 2.

    What I get in result for months 10-12 is only 1. For example, for October data, I get:

    Project, Year, Month, Amt

    P1, 2014, 1, 100.0

    Any idea what is going on?

    Thanks


    Pedja


    Tuesday, August 5, 2014 8:49 PM

Answers

  • you can use string function to pad zero to integers 1,2,...,9 to make it 01,02,...,09. There is Replicate function to pad zero and then use Right function to get only 2 digits from right. check the link http://social.msdn.microsoft.com/forums/sqlserver/en-US/fc4aece3-14d6-49e9-bc07-8e3c4743a225/how-to-pad-with-zeros-to-a-integer-in-ssis

    Wednesday, August 6, 2014 7:57 PM
  • I set Month as "Pivot Key Column Name", and Pivot Key Values to: 1,2,3,...,12.
     
    In Advance Editor, I set Month column data type to DT_STR, with length = 2.
    

    Thanks. I actually found a way to do it directly in unpivot transformation:

    If I set pivot key values to: 01, 02, 03, 04,...,12, and in advance editor I set column type to DT_STR, with length = 4 (?!?!, setting it to 2 will cut one character, so 10,11, or 12 will become 1), it will peform exactly as I wanted. The only "issue" is that transformation shows warning because of unmatching size and possible truncation (destination month column is char(2), and output column is char(4)), but since it will never happen I don't mind it.

    


    Pedja

    Thursday, August 7, 2014 8:00 PM

All replies

  • Just an update...

    If I specify Month column to integer (in Advanced Editor), I get everything OK. However, problem is that in destination table I still need "09", and not "9" as month.

    I want to avoid solution where I update destination table's month column (Execute SQL task). I want to do in on the fly, before loading data to destination table.

    Any ideas?


    Pedja


    Wednesday, August 6, 2014 6:28 PM
  • you can use string function to pad zero to integers 1,2,...,9 to make it 01,02,...,09. There is Replicate function to pad zero and then use Right function to get only 2 digits from right. check the link http://social.msdn.microsoft.com/forums/sqlserver/en-US/fc4aece3-14d6-49e9-bc07-8e3c4743a225/how-to-pad-with-zeros-to-a-integer-in-ssis

    Wednesday, August 6, 2014 7:57 PM
  • I set Month as "Pivot Key Column Name", and Pivot Key Values to: 1,2,3,...,12.
     
    In Advance Editor, I set Month column data type to DT_STR, with length = 2.
    

    Thanks. I actually found a way to do it directly in unpivot transformation:

    If I set pivot key values to: 01, 02, 03, 04,...,12, and in advance editor I set column type to DT_STR, with length = 4 (?!?!, setting it to 2 will cut one character, so 10,11, or 12 will become 1), it will peform exactly as I wanted. The only "issue" is that transformation shows warning because of unmatching size and possible truncation (destination month column is char(2), and output column is char(4)), but since it will never happen I don't mind it.

    


    Pedja

    Thursday, August 7, 2014 8:00 PM