Insert Row number to non-identity column in SSIS / SQL
I have to load a Fact Table with data from 3 different groups. Fact Table Structure RowNum int, GroupName varchar(50), CostOnDate datetime, Cost decimal(15,0) Given below is data available in 3 different excel files. Excel 1: Apple 20-Jan-2011 100.00 Apple 21-Jan-2011 110.00 Excel 2: Orange 20-Jan-2011 111.00 Orange 21-Jan-2011 111.00 Excel 3: Grapes 20-Jan-2011 50.00 Grapes 21-Jan-2011 50.00 I have to load these 3 excel data to Fact Table. So I have 3 different DFD in SSIS package to load data. Now my problem is how to load RowNum column. All these data use delete table logic and then insert data. so i cant use RowNum as 'identity' column. I cant even use Row numberTransformation as Idont know how many rows an excel wll have at one time. some how want to add the Row Number through a SSIS package or through some INSERT SQL query if possible. Please give me a possible way of making tis work. thanks in advance.
May 17th, 2011 7:49am

I tried the below query but inserted double (12 rows0 the number of records. ;( insert into FactTable (RowNum) select row_number() over (order by (select 0)) FROM FactTable
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 8:03am

I think you are using Excel Source for import excel file and OLEDB Destination for load excel data into table in DATAFLOW task, right? Put Script Component between Excel Source and OLEDB Destination and mapped Script Component Output column with RowNum column of your table. Goto here for step as well as script which will give you ROWNUM value. You can also try OLEDB Command for generate ROWNUM value. Thanks, Kapil Khalas - Database Developer
May 17th, 2011 8:23am

I found out a solution which works for my scenario. Since the source files are loaded in different data flow tasks, Ihave decided to create a view on the table and add row_number() over (order by (select 0)) as RowNumber column to the view. So, in which ever order the data is loaded, the view will give me the row number in order with out any confusions. (Initially I was trying to add this column to the Fact Table which was not possible. The row numbers were not in proper order. Then i decided to use the view with the new column) Thanks for you effort.
Free Windows Admin Tool Kit Click here and download it now
May 18th, 2011 12:41am

I think you are using Excel Source for import excel file and OLEDB Destination for load excel data into table in DATAFLOW task, right? Put Script Component between Excel Source and OLEDB Destination and mapped Script Component Output column with RowNum column of your table. Goto here for step as well as script which will give you ROWNUM value. You can also try OLEDB Command for generate ROWNUM value. Thanks, Kapil Khalas - Database Developer Thanks for your response. This was useful for me in another scenario.
May 18th, 2011 12:42am

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

Other recent topics Other recent topics