SSIS - importing flat files - how to dynamically unpivot the data

Question

SSIS - importing flat files - how to dynamically unpivot the data

inampudianil

Ten Centuries

Points: 1274
More actions
May 22, 2012 at 3:49 am

#261245

Hi Team,
i received a flat file with the structure like this name and last couple of dates with quantity
eg:
Name | 22/05/2012 | 21/05/2012
=====================================
product1 | 30 |40
product2 | 0 |30
product3 | 25 |24
Now my destination table schema is
Name,date,Qty
eg: Name | Date | qty
============================
Product1 |22/05/2012 |30
Product1 |21/05/2012 |40
Product2 |22/05/2012 |0
Product2 |21/05/2012 |30
Product3 |22/05/2012 |25
Product3 |22/05/2012 |24
Now,how can i achive this in SSIS...plzz help?
Thnx
Anil

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113509 More actions · Answer 1

Could you not use the unpivot task in a DFT to do it?

Could also do a Google for "dynamic unpivot tsql", as it looks like the SSIS Junkie has already done such a thing in code

http://sqlblog.com/blogs/jamie_thomson/archive/2010/05/29/dynamic-unpivot-ssis-nugget.aspx

inampudianil Ten Centuries Points: 1274 More actions · Answer 2

Hi Anthony,

Thanks for your help...i will try implementing the same..

Regards

Anil

inampudianil Ten Centuries Points: 1274 More actions · Answer 3

Hi Anthony,

In my case, I can't use the unpivot because.. the dates which i mentioned in column headers will vary at each and every point of time.. in this case.. I need to change my package every time(for new dates column headers) and for selecting the date and pivot key...

Is there any other way that i can do dynamically..

Thanks

Anil

Ant-Green SSC Guru Points: 113509 More actions · Answer 4

Yes, take a look at the link and there is plenty of good Google articles on it as well.