Now I am new to all of this so I have no clue if I have even went about this the right way or if there is an even better way to do this. Im trying to see what my options are. Here is the process
1. Copy data from Oracle to SQL Server (Sales Order Headers)
2. For Each SO Header that is copied over I need to copy over the SO Line details for that Header.
This is what I have been currently working on and I just wanted to bounce this off you guys and see if there is not a better way.
1. Execute SQL Task - Select SOHDRID From SOHDR (Local SQL Table) Where Posted = 1
2. Foreach Loop Container
a. Data Flow Task
i. OLE DB Source - Select From SOLINE (Oracle Table) Where SOHDRID = @SOHDRID
ii. OLE DB Destination - Insert Into Table (SQL Table)
This is the structure that im using and this works but the problem is that its slow, very slow as it has to iterate over each record, select the new data and then insert into the table.
I would assume you could do something like this possibly with the Execute SQL Task (Possibly using a linked server??):
Insert into SQL Table(SoLine, Debit_Amt, Credit_Amt, Year, Period)
Select SoLine, Sub(Debit_Amt), Sum(Credit_Amt), Year, Period
From SoLine (Oracle)
Where SoLineHdr = @SOHDRID
Group By SoLineHdr, Year, Period
Again just wanted to bounce this off you some people and see if im going about this the right way or if there is a better way.