Copy SoLine Details For Each SoHdr

Last Post 24 Jul 2008 07:06 AM by lmf232s. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
lmf232s
New Member
New Member

--
23 Jul 2008 11:15 AM
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.

Thanks.


lmf232s
New Member
New Member

--
24 Jul 2008 07:06 AM
Yes another SQL/Oracle evnironment but on the bright side I work for different clients now so once this job is over Im on to a new set up.

I suppose its possible on the Insert to do exactly what you posted. Im still thinking about if that will work or not and again im not as familiar with DTS/SSIS so im treading lightly and trying to soak it all up. How have I made it 5 years without knowing this stuff?

Let me explain a bit more about whats happening. Basically We are extracting data from Oracle and putting it into SQL server as the intermediate DB (This is were we will work with the data). Then from SQL server we are going to move it into MS PerformancePoint.

This will be a nightly job that runs (Possibly hourly or several times a day) to sync the data (and yes its painfully slow iterating 1 record at a time). Were going to do a match merge as we pull data from Oracle to SQL Server and either do an Insert, Update, or a Delete. We select all the hdr records from oracle based on certain conditions as well as all the hdr records from SQL Server. We then merge the two sets of data and 1. Update - If any the sql and oracle fields have changed 2. Insert - If we have oracle data but no sql data 3. Delete - If we have sql data but no oracle data.

Currently on the insert node (OLE DB Destination) we are using a 'Table or View - fast load' on the destination table. I suppose I could change this to a SQL Command but at this point in time I do not have the detail records for the hdr record yet. So could I possibly select the hdr record and the detail records in the initial OLE DB Source Command?


You are not authorized to post a reply.

Acceptable Use Policy