amiBhat


hI,

i have this particular problem with the unpivot.The below is my flat file source.The dates can go upto 130 columns.this count can also vary.SM,SR,SB are again values repeating for diff instrument.They are the values of the instrument on the particular dates.This is a snap shot of one feed.Other feeds may have the dates differing.How do i read this file.

Problem 1:If i skip the first row and unpivot the 2nd row,then with the new feed,with new dates my SSIS package will bomb as it will not find the col names.

Problem 2:IF i uncheck the "Use first row as column headers" then the problem 1 is solved but the o/p will be

20080101

20061102

20061103
1.2

1.3

1.2.

1.5

.....and so on..

IS there any other way to fix this.These are feeds with the spread values of instruments on particular dates.Please help.

RUN 2.01E+11 132238 0 45
INSTRID DATATYPES 20081101 20061102 20061103
Z03369 SM 1.1 1.2 1.3
Z03369 SB 1.3 1.3 1.7

Z03369 SR 2 3 4

Z81910 SM 1.1 1.2 1.3
Z81910 SB 1.3 1.3 1.7




Re: WANT TO SKIP ROWS AAND ALSO UNPIVOT

Anjan Das MSFT


You'd likely need to use a script component.

You can find a good example at http://blogs.conchango.com/jamiethomson/archive/2006/12/13/SSIS-Nugget_3A00_-Unpivoting-using-the-script-component.aspx







Re: WANT TO SKIP ROWS AAND ALSO UNPIVOT

jwelch

Jamie's example is very good. I put up an example that is a variation on his a little while back: http://agilebi.com/cs/blogs/jwelch/archive/2007/05/18/dynamically-pivoting-columns-to-rows.aspx

This one shows how to deal with the varying number of columns in your source file.







Re: WANT TO SKIP ROWS AAND ALSO UNPIVOT

amiBhat

the example was good.But my problem still remains.May be i was not clear enuf.

The flat file is as follows

Col1 col2 col3 col4 col5 col6

InstrID Spread 20040404 20040505 20040606 20040707

1 SM 4.5 5.6 6.7 7.8

1 SR 2.4 2.5 4.5 6.7

1 VV 4 4 5 6

2 SM 4.5 5.6 4.4 5.8

2 SR 4.5 5.2 4.4 5.8

The o/p wanted is

InstrID Date Spread Value

1 20040404 SM 4.5

1 20040505 SM 5.6

1 20040606 SM 6.7

and so on.....

I am not removing the col1,col2 ...etc because the dates will be changing in different feeds.So i cant fix the column names.

thanks

ami





Re: WANT TO SKIP ROWS AAND ALSO UNPIVOT

jwelch

Not sure what the problem is - this is precisely what the example I pointed you to handles, unless I am missing something in your requirements.




Re: WANT TO SKIP ROWS AAND ALSO UNPIVOT

amiBhat

i have to unpivot on the values in second row and not first row.Because the values in 2 row are going to change with different feeds.

Thanks

Ami





Re: WANT TO SKIP ROWS AAND ALSO UNPIVOT

jwelch

You'll need to alter the script to retain the row with dates in it, and reuse the values on subsequent rows.