Variable Input Columns to be Unpivoted in the Unpivot Transformation
The pivot/unpivot operations are somewhat limited in that you have to have a static structure for these to work well in SSIS. Depending on your data volume and requirements it might be better to stick to a structure SSIS understands and then use a view to provide pivoted access, or perhaps only query the last X years, so you always have X set of columns to pivot.
October 11th, 2011 6:32am

All, I'm in the process of learning Pivot/Unpivot through SSIS. I downloaded the sample data to work with from the link below and I was able to create a table and load the unpivoted data. http://explore.data.gov/Foreign-Commerce-and-Aid/U-S-Overseas-Loans-and-Grants-Greenbook-/5gah-bvex CREATE TABLE [dbo].[Economic_Assistance]( [Country_Name] [nvarchar](300) NULL, [Program_Name] [nvarchar](500) NULL, [Aid_Year] [nvarchar](500) NULL, [Aid_Amount] [money] NULL ) While configuring the "Unpivot Transformation Editor", I noticed that for all the Aid Year (from FY1946 thru FY2009), the "Destination Column" and the "Pivot Key Value" need to be specified. THis is fine as long as we know the number of years in advance. But in reality, this is not true. Suppose, in 2012, I get another file whose Aid Year value ranges from FY1920 thru FY2011, do I have to create another Unpivot transformation inorder to load. Or, is there a way where we can specify that beyond the 2nd, all columns need to be treated as Input Columns. Any ideas. Regards, MS
Free Windows Admin Tool Kit Click here and download it now
October 15th, 2011 5:00pm

The pivot/unpivot operations are somewhat limited in that you have to have a static structure for these to work well in SSIS. Depending on your data volume and requirements it might be better to stick to a structure SSIS understands and then use a view to provide pivoted access, or perhaps only query the last X years, so you always have X set of columns to pivot.
October 15th, 2011 11:37pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics