HI Guys, I have got a denormalised data structure as below:- Category : Year1 : Year2 : Year3... so on A : 12 : 12 : 23 B : 34 : 43 : 65 Have to convert to Category Year Value A Year1 12 A Year2 12 A Yaer3 23 . . There could be blanks in the data as I am reading it from excel source. I am aware I can use unpivot but can you suggest me something else. Unpivot is throwing error in my case which has no error explanation. Cheers.

You could use the data flow task to put the data into a table in SQL Server and then use the UnPivot command in TSQL using an Execute SQL Task in the control flow after the Data Flow. Thanks, FindTim

Un Pivot is really the way to go here. I know the Books On Line documentation is not real helpful. Can you give us a glimpse of how you set up your Un Pivot transform, and what the error is you got?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Hello, Here are the steps to achieve this by using unpivot transformation. I assume for now , we having only foure coulumns(Category, Year1,Year2,Year3) Steps: 1- Connect to unpivot transformation.... double click the unpivot transformation. You will see two things on the top , Name PassThrough Category CheckThisBox CheckthisBox(Year1) Leave as it is CheakthisBox(Year2) leave as it is CheckThisBox(Year3) leave as it is Now under this you will see Input Column Destination Column Pivot Key Column Year1 Year Year1 Year2 Year Year2 Year3 Year Year3 ... ... If you have more years you can put it here and you have to checkBox as i did for these three years in step 2. You do not need to change anything else. you are done. here is the link , where you can see the diagram. http://www.proteanit.com/b/2007/08/21/ssis-unpivot-component/ Thanks!

TOdd, I did exactly what Aamir has suggested and getting the eror:- [Unpivot [16]] Warning: The parameter is incorrect. [Unpivot [16]] Error: Failure when trying to pivot the source records. [DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Unpivot" (16) failed with error code 0xC020208F. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. My column names are like "2002/2003-01", "2003/2004-02".. What does 16 in the error signify? Cheers.

All, wanted to add that I tried Unpivot execute sql task and it worked as a treat. I would still like to get this unpivot transformation working. Cheers.

Hi, 16 is the ID number of the component. The error code is :"error code 0xC020208F". And as your post of the error message says it failed to pivot the records due to a parameter not being set correctly. In order to dig into the issue with more detail it would be helpful to see the UnPivot Transformation Editor screen shoots so that we can review the properties that were set. This post describes how to post pictures to the forum. http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/d11ab7f0-6ba9-4b60-a674-be772b655a55 Thanks, FindTim

Hi, Just wondered if there was any resolve to this issue? I have the same error code reported when unpivoting on several fields, yet passing through all remaining fields (a fair number). Have narrowed the error down to a DT_NTEXT type field (there is only one in the collection of fields passing through or unpivoting on)Without passing this field through the unpivot works a treat. Generated a C# script to perform the unpivot meanwhile whilst i investigate further but would like to find out the cause. Not vital for this field to be passed through but wondered if a bug has been raised or if anyone else has experienced this. Running SQL Server 2008 on Windows Server 2008 Standard. Thanks Rich

I have the same issue here: UnPivot does not seem to work when passing through fields of type DT_TEXT. I have an OLE DB source connecting to a SQL Server 2008 instance. When using UnPivot and passing through the DT_TEXT field, I have the same error messages that have been described above. You could work around it by multicasting, not passing through the DT_TEXT fields and then joining them again. Not efficient, but in case you do require the DT_TEXT fields this should work..