July 19, 2011 at 2:35 am
Dear All,
I have been having sleepless night with this for more than 2 weeks. This also made me re-write codes many times.
Am using Sql sever 2005 presently.
I initially used SSIS to move data from ORACLE to SQL SERVER, record count slightly above 1 million rows. And runs for 4 hrs. But the whole process returns lesser rows. Do I have to tweak something in the process? Do I need to do something to destination?
I even used linked server but still get the same output. What made matter worst is I always have varying record count at every 4 hours.
Kindly help.
July 19, 2011 at 2:43 am
Is it always after exactly 4 hours?
Have you checked the event log?
Does the job fail?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 19, 2011 at 2:52 am
Phil,
Many thanks Boss ...
Yes, It's runs 4 hours. There is a particular function running the process slow...
July 19, 2011 at 3:10 am
Olalekan Adewale (7/19/2011)
Phil,Many thanks Boss ...
Yes, It's runs 4 hours. There is a particular function running the process slow...
OK. Please answer my other questions and describe what you mean by 'particular function'.
If you get any error messages, please post them too.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 19, 2011 at 3:44 am
Phil,
The function runs well.
The function returns daily/average balance for each records, based on account_id, from_date and to_date. it's an oracle function..
CREATE OR REPLACE function getAvgBal(cAcid varchar,sDate in date, eDate in date) return varchar
as
startDate eab.eod_date%type;
endDate eab.eod_date%type;
tempDate date;
noOfDays number;
creditBal number;
debitBal number;
tempBal number;
begin
startDate := sDate; --to_date(sDate,'DD-MON-YY');
endDate :=eDate ; --to_date(eDate,'DD-MON-YY');
tempDate := startDate;
creditBal := 0;
debitBal := 0;
tempBal := 0;
noOfDays :=endDate-startDate+1;
while tempDate <= endDate loop
begin
select value_date_bal into tempBal from balance where acid=cAcid
and eod_date<=tempDate and end_eod_date>=tempDate;
exception
when NO_DATA_FOUND then
tempBal := 0;
end;
if tempBal<=0 then
debitBal := debitBal + tempBal;
else
creditBal := creditBal + tempBal;
end if ;
tempDate := tempDate + 1;
end loop;
return to_char(round(debitBal/noOfDays,2)||'#'||round(creditBal/noOfDays,2));
end;
/
I Checked SQL Sever log and Windows Event viewer no error, but I don't have access to oracle side.. I only connect to Oracle base on my script.
And am able to execute my script successfully from oracle end. The issues is between oracle and sql server. I think the pipeline or network.
Many thanks.
July 19, 2011 at 1:28 pm
Are you doing a straight table to table transfer? Or are there any filters either in the query you're using to grab the rows from your oracle table or in the dataflow that might be reduving the row count? Also are there any constraints on the destination table that might be excluding some rows(although you should see errors for that if you haven't changed the max error count)?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply