none
Dynamically Unpivot In SSIS

    Question

  •  

    I need to dynamically unpivot some set of columns in my source file. Every month there is one new column and its set of Values. I want to unpivot it without editing my SSIS packages that is deployed...

    Can you please help me ???

     

    Regards

    Shiv

    Friday, May 28, 2010 7:29 AM

Answers

All replies

  • Not possible in the SSIS dataflow I'm afraid. The SSIS dataflow is heavily bound to the metadata and destinations defined at design-time so if a new column turns up in the source you would (probably) get an error.

     

    -Jamie


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Friday, May 28, 2010 8:19 AM
  • Yes Thomson..

    Always I used to think why the ETL flow in SSIS is so much bounded with the metadata that's defined at design time..

    I dont know how to achieve this with the existing transformations.. please help

    Regards

    Shiv

    Friday, May 28, 2010 9:16 AM
  • Shiv,

            As suggested by Jamie, Its not possible with Transformations since your schema keeps on chaning so the underlying metadata will throws error.

    You have to think of some other workarunds like,

    Using alter script add a field when new one needed.

    Do the field mapping using stored procedure.

    Friday, May 28, 2010 9:28 AM
  • Yes Thomson..

    Always I used to think why the ETL flow in SSIS is so much bounded with the metadata that's defined at design time..

    Performance. it makes the dataflow lightning quick.

    I dont know how to achieve this with the existing transformations.. please help

    Regards

    Shiv

    Like I said, its not possible with the existing transformations. You will need to import all the columns that you want to unpivot as a single column (explore the Ragged Right feature of the Flat File Source Adapter) and then write your unpivoting logic inside a script component or (if you're brave) a custom component.

     

    -Jamie


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Friday, May 28, 2010 9:29 AM
  • Ok Jamie...

    Let me write a script to read my row & unpivot it.

     

    Regards

    Shiv

    Friday, May 28, 2010 9:50 AM
  • Ok Jamie...

    Let me write a script to read my row & unpivot it.

     

    Regards

    Shiv


    OK cool. I have thrown together a demo of unpivoting using a script component. If you want a copy of it email me at

    jamie [at] jamie-thomson.net


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Friday, May 28, 2010 10:32 AM
  • Ok Jamie...

    Let me write a script to read my row & unpivot it.

     

    Regards

    Shiv


    I've written up an example here: Dynamic Unpivot
    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Saturday, May 29, 2010 6:14 AM