Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help required in SSIS 2008 Expand / Collapse
Author
Message
Posted Tuesday, September 3, 2013 11:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 11:11 AM
Points: 4, Visits: 12
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"

A Req A 4
B Req B 2
C Req C 1

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.

Post #1491045
Posted Monday, September 9, 2013 1:17 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:25 AM
Points: 445, Visits: 565
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"

A Req A 4
B Req B 2
C Req C 1

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)
Post #1492672
Posted Monday, September 9, 2013 2:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 11:11 AM
Points: 4, Visits: 12
Hello,

Thank you for replying on the post.

Can you also please help me with the T-SQL script to insert data
Post #1492691
Posted Monday, September 9, 2013 3:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 13,252, Visits: 10,135
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1492712
Posted Monday, September 9, 2013 5:29 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1492734
Posted Monday, September 9, 2013 6:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 11:11 AM
Points: 4, Visits: 12
Thank you Phil...
Great idea. It solves the problem.
Post #1492743
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse