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