Link to home
Start Free TrialLog in
Avatar of rajvja
rajvjaFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Insert and Update in SSIS

Hi,

    I have a requirement that I need to generate a csv file from the data.
I need to get data from two tables from the same source. I can't use join coz there will be a very complex join and I am afraid it won't get the expected results.

Ok, first I need to insert into a temp table and I want to do Update(s) on this temp table from multiple tables and joins. Then from the temp table, csv file is generated.

What is the best way to design this?

What I am doing is, DFT-> 2 OLEDB Sources -> UNION ALL -> OLEDB Destination
CONTROL FLOW -> Exec SQL Task -> First Update
CONTROL FLOW -> Exec SQL Task -> Second Update
CONTROL FLOW -> Exec SQL Task -> Third Update
CONTROL FLOW -> Exec SQL Task -> Fourth Update

Again DFT - > generate CSV

Is there any other best way to do?

thanks
Avatar of PedroCGD
PedroCGD
Flag of Portugal image

Use the OLEDB Command inside dataflow to update each record in the temp table...
Avatar of rajvja

ASKER

Hi,

   My update is like this

Update temptable set col2=tab.col2
from temptable
inner join
(
select statement with join
) tab
on <join condition>

After this OLEDB command, I need to execute another. Hope it work if the above works.
And after inserting into tempTable in DFT, can we get output path from OLEDB destination?
And again how do we get OUTPUT columns from OLEDB command for the above updates.
ASKER CERTIFIED SOLUTION
Avatar of PedroCGD
PedroCGD
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rajvja

ASKER

hanks