Hi Team, i received a flat file with the structure like this product 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... Thanks Anil Anil Inampudi

By using the Unpivot Transformation? http://www.bimonkey.com/2009/07/the-unpivot-transformation/MCTS, MCITP - Please mark posts as answered where appropriate.

Hi Koen, Thanks for the fast reply..... Here i can't use unpivot because.. the dates will Change at every point of time.. and i cannot modify the package for the next loads...So Can i able to implement in any other way dynamically.. Thanks AnilAnil Inampudi

Hi Anil, I think you could try to full load the file to a table and the data type of all columns should be NVARCHAR, and process the first row as a date-data source, the rest should be the product - qty data source; Then...sperate it to be two: I just test it as SQL way, it works; DECLARE @T1 TABLE ( PRODUCT VARCHAR(10), TM DATE, IDX INT ) DECLARE @T2 TABLE ( PRODUCT VARCHAR(10), QT INT, IDX INT ) INSERT INTO @T1 VALUES('product1','2012-05-22',1),('product1','2012-05-21',2), ('product2','2012-05-22',1),('product2','2012-05-21',2), ('product3','2012-05-22',1),('product3','2012-05-21',2) INSERT INTO @T2 VALUES('product1',30,1),('product1',40,2), ('product2',0,1),('product2',30,2), ('product3',25,1),('product3',24,2) /** SELECT * FROM @T1 product1 2012-05-22 1 product1 2012-05-21 2 product2 2012-05-22 1 product2 2012-05-21 2 product3 2012-05-22 1 product3 2012-05-21 2 **/ /** SELECT * FROM @T2 product1 30 1 product1 40 2 product2 0 1 product2 30 2 product3 25 1 product3 24 2 **/ SELECT t1.PRODUCT, t1.TM, t2.QT FROM @T1 AS t1 INNER JOIN @T2 AS t2 ON t1.PRODUCT = t2.PRODUCT AND t1.IDX = t2.IDX /** Result Shows: product1 2012-05-22 30 product1 2012-05-21 40 product2 2012-05-22 0 product2 2012-05-21 30 product3 2012-05-22 25 product3 2012-05-21 24 **/ I'm not sure if it's a good way, just hope it helps.

A script component is also possible. Read the first row as data and not as a header. Store the two dates in two variables inside the script component and discard that row. Split every row into two seperate rows (product - qty1 - date1 and product - qty2 - date2) and add both rows to the output buffer.MCTS, MCITP - Please mark posts as answered where appropriate.

Hi, Thanks for your answer for both koen and Simon.. @Simon: In table variable( @T1) i guess, you have hard coded some of the dates(which are like column headers)..but in my case..how can i transform the Column headers to Data rows (as you hard coded).Is there any way..that i can get the result set like table variable( @T1) ??.. if yea,plz share it for me. @koen:If possible, Can you share a sample package for me... Thanks in Advance Anil InampudiAnil Inampudi

I don't have simular code lying around and I'm not going to write it for you, but this blog posts explains how to set-up a synchronous script component with multiple outputs: http://consultingblogs.emc.com/jamiethomson/archive/2005/09/05/SSIS-Nugget_3A00_-Multiple-outputs-from-a-synchronous-script-transform.aspxMCTS, MCITP - Please mark posts as answered where appropriate.

Hi Anil, @T1 and @T2 just an example what your testing data look like. You can load all data into a table.. Column1 , Column2, Column3 Name | 22/05/2012 | 21/05/2012 product1 | 30 |40 product2 | 0 |30 product3 | 25 |24 Then just transfer them to @T1 and @T2 by SQL way... Or you can refer to Koen by using script.

I just made a test in SSIS and SQL...It works.. Everything you need to do is to load the data from the flat file and convert the date.. The SQL I have tested, the result is what you want, hope it helps. My Testing File.. Name |2012-05-22|2012-05-21 product1|30 |40 product2|0 |30 product3|25 |24 /** Load data from flat files to Table T. CREATE TABLE [dbo].[T]( [Column 0] [nvarchar](50) NULL, [Column 1] [nvarchar](50) NULL, [Column 2] [nvarchar](50) NULL ) ON [PRIMARY] Result should be.. Column 0 Column 1 Column 2 Name 2012-05-22 2012-05-21 product1 30 40 product2 0 30 product3 25 24 **/ DECLARE @DATE TABLE ( DATES DATE, IDX INT ) INSERT INTO @DATE SELECT CAST([COLUMN 1] AS DATE) , 1 FROM T WHERE [Column 0] = 'Name' UNION SELECT CAST([COLUMN 2] AS DATE), 2 FROM T WHERE [Column 0] = 'Name' SELECT * FROM @DATE /** DATES IDX 2012-05-21 2 2012-05-22 1 **/ DECLARE @Product TABLE ( Product NVARCHAR(50), QT INT, IDX INT ) INSERT INTO @Product SELECT [Column 0], [Column 1], 1 FROM T WHERE [Column 0] <> 'Name' UNION SELECT [Column 0], [Column 2], 2 FROM T WHERE [Column 0] <> 'Name' SELECT * FROM @Product /** Product QT IDX product1 30 1 product1 40 2 product2 0 1 product2 30 2 product3 24 2 product3 25 1 **/ SELECT P.Product, D.DATES, P.QT FROM @DATE AS D INNER JOIN @Product AS P ON d.IDX = P.IDX /** The result is what you want.. product1 2012-05-22 30 product1 2012-05-21 40 product2 2012-05-22 0 product2 2012-05-21 30 product3 2012-05-22 25 product3 2012-05-21 24 **/

I just made a test in SSIS and SQL...It works.. Everything you need to do is to load the data from the flat file and convert the date.. The SQL I have tested, the result is what you want, hope it helps. My Testing File.. Name |2012-05-22|2012-05-21 product1|30 |40 product2|0 |30 product3|25 |24 /** Load data from flat files to Table T. CREATE TABLE [dbo].[T]( [Column 0] [nvarchar](50) NULL, [Column 1] [nvarchar](50) NULL, [Column 2] [nvarchar](50) NULL ) ON [PRIMARY] Result should be.. Column 0 Column 1 Column 2 Name 2012-05-22 2012-05-21 product1 30 40 product2 0 30 product3 25 24 **/ DECLARE @DATE TABLE ( DATES DATE, IDX INT ) INSERT INTO @DATE SELECT CAST([COLUMN 1] AS DATE) , 1 FROM T WHERE [Column 0] = 'Name' UNION SELECT CAST([COLUMN 2] AS DATE), 2 FROM T WHERE [Column 0] = 'Name' SELECT * FROM @DATE /** DATES IDX 2012-05-21 2 2012-05-22 1 **/ DECLARE @Product TABLE ( Product NVARCHAR(50), QT INT, IDX INT ) INSERT INTO @Product SELECT [Column 0], [Column 1], 1 FROM T WHERE [Column 0] <> 'Name' UNION SELECT [Column 0], [Column 2], 2 FROM T WHERE [Column 0] <> 'Name' SELECT * FROM @Product /** Product QT IDX product1 30 1 product1 40 2 product2 0 1 product2 30 2 product3 24 2 product3 25 1 **/ SELECT P.Product, D.DATES, P.QT FROM @DATE AS D INNER JOIN @Product AS P ON d.IDX = P.IDX /** The result is what you want.. product1 2012-05-22 30 product1 2012-05-21 40 product2 2012-05-22 0 product2 2012-05-21 30 product3 2012-05-22 25 product3 2012-05-21 24 **/

Hi Anil, Please vote my answer as helpful if the solution helps you.

Hi Anil, Please vote my answer as helpful if the solution helps you. Don't worry, the forum moderators will do it if Anil doesn't.MCTS, MCITP - Please mark posts as answered where appropriate.

Hi Simon/Koen, Sorry for the belated reply... and thanks for your answers... i have tried a sample solution for this with dynamic sql. the tbale which i have created is a stagin table..it will be created at starting and dropped at last(based on the data).. create table Testa (productid int,[6/5/2012] int,[6/6/2012] int,) insert into Testa select 1,30,20 union all select 2,76,98 union all select 3,54,89 union all select 4,67,33 union all select 5,23,76 union all select 6,45,65 --Select * from Testa declare @Table_name varchar(100), @col varchar(max), @sql nvarchar(max),@col1 varchar(max) set @Table_name ='Testa' set @col =(select stuff((select +','+ quotename( ''+name+'') from (select [name] from syscolumns where id=object_id(@Table_name) and colid >1 ) as a for XML PATH('')),1,1,'') ) set @col1 =(select stuff((select +','+ quotename( ''+name+'') from (select [name] from syscolumns where id=object_id(@Table_name) ) as a for XML PATH('')),1,1,'') ) set @sql = 'select productid,[Date],Qt from (select '+ @col1+' from '+@Table_name+') p unpivot (Qt FOR [Date] IN ('+@col +') ) as unpvt' exec sp_executesql @sql I have tried creating a sproc and call it in control flow and the results are saved to the object variable.. from object variable.. i am reading and writng to destination table ...... any way thanks for your help...keep rocking.. Regards Anil Inampudi Anil Inampudi