rajvja
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
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
Use the OLEDB Command inside dataflow to update each record in the temp table...
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hanks