Hi, I want to convert columns to rows in SSIS. Source is Excel sheet and target is SQL table. I am using SSIS 2008. The complication is, the number of columns are bound to keep increasing. Can this be achieved in SSIS? I have provided a sample of the source data looks in the excel file. Name, spec1 & Spec2 are 3 rows in 'A' column in excel. The values for Product1 appears in Column 'B and Product2 in 'C'. If a new product is introduced, it would appear in column 'D'. So the source has a growing number of columns, and static number of rows. Source Data in excel: Name Product1 Product2 ... Spec1 a b Spec2 c d Spec3 e f Target Data in Table: Product Spec1 Spec2 Spec3 Product1 a c e Product2 b d f Please advice, how can this be achieved? Thanks.

Following is a related article. Let us know if helpful. Dynamic Unpivot : SSIS Nugget A question on the SSIS forum earlier today asked: 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 Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Thanks for that. The URL speaks about using flat file as the source. Mine is an excel file. Any idea how this can be achieved?

You can use a "Excel Source" instead of a flat file source. The source component is available in the toolbox in BIDS. Hope this helpes. Cheers!! Muqadder.

True. But the excel source ties itself to the existing list of columns, when we define the source transformation. But for me, the column list is a growing one...

What you are trying to do falls into the area of Dynamic SQL. You upload an Excel sheet with varying number of columns into a new staging table. From there on you can use database metadata and dynamic SQL to process it. Dynamic SQL links with examples: http://www.sqlusa.com/bestpractices/training/scripts/dynamicsql/ http://www.sqlusa.com/bestpractices/dynamicsql/ Uploading Excel with new table create example at the following page:: 22. SELECT INTO import data from Excel worksheet with distributed query . http://www.sqlusa.com/bestpractices2005/createemptytable/ Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM