Insert and Update in SSIS

rajvja
rajvja used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Use the OLEDB Command inside dataflow to update each record in the temp table...

Author

Commented:
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.
Commented:
You want to do all the work using SQL statment or inside SSIS as blocks?

If in SSIS in your dataflow... you read the data from sources and can use the merge join, next update data based on condition using OLEDB command. In OLEDB comand you can call a stored procedure and can get the output parameter from them...Example SQL Statment inside OLEDB Command:  EXEC MyStoredProcedure ?,?,? OUTPUT

Author

Commented:
hanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial