September 3, 2013 at 11:12 am
Hello,
I am new to SSIS. Can anyone help me provide solution for below mentioned scenario…
I want to transfer data from Excel file to SQL server table based on the values of one column of the source, the rows needs to be duplicated in the destination (SQL table).
Example:
Source (Excel file)
"Req ID""Desc""Number"
AReq A4
BReq B2
CReq C1
Destination (SQL Table) – Rows needs to be duplicated based on the “Number” column in the source and to make each row is unique, Req Number needs to be added for each row.
"Req ID" "Req Number""Desc"
A 1 Req A
A 2 Req A
A 3 Req A
A 4 Req A
B 1 Req B
B 2 Req B
C 1 Req C
Thanks.
September 9, 2013 at 1:17 am
patel.ritesh.r (9/3/2013)
Hello,I am new to SSIS. Can anyone help me provide solution for below mentioned scenario…
I want to transfer data from Excel file to SQL server table based on the values of one column of the source, the rows needs to be duplicated in the destination (SQL table).
Example:
Source (Excel file)
"Req ID""Desc""Number"
AReq A4
BReq B2
CReq C1
Destination (SQL Table) – Rows needs to be duplicated based on the “Number” column in the source and to make each row is unique, Req Number needs to be added for each row.
"Req ID" "Req Number""Desc"
A 1 Req A
A 2 Req A
A 3 Req A
A 4 Req A
B 1 Req B
B 2 Req B
C 1 Req C
Thanks.
first you can load your data in a staging table which is as it is as your excel file.
then you can write a T-sql script that inserts your data in the destination tables(you can use Execute sql task for this)
September 9, 2013 at 2:47 am
Hello,
Thank you for replying on the post.
Can you also please help me with the T-SQL script to insert data
September 9, 2013 at 3:51 am
You could also do it with some .net coding in an asynchronous script component.
You read the value from number and use it in a for loop.
In each iteration of the loop, you output a row with the current value of the iterator.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 9, 2013 at 5:29 am
patel.ritesh.r (9/9/2013)
Hello,Thank you for replying on the post.
Can you also please help me with the T-SQL script to insert data
You could use a 'numbers' table to do this (1,2,3,4,5,6,7,8,9,...).
Join to the numbers table on
numbers.number <= stagingtable.NumOfRowsToCreate
And that will give you the required number of rows.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 9, 2013 at 6:03 am
Thank you Phil...
Great idea. It solves the problem. 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply