July 22, 2009 at 7:12 am
Hi,
Have an SSIS package that imports data from Oracle and was running fine in DTS (2000) but now we have taken into SQL 2005 SSIS.
We have a stored procedure that creates a line of dynamic SQL ..the select part is coded but the location and which linked server to use come from a SQL table...
When we ran this into SSIS we found that we could not see the columns ..so searched and found that we needed to add set nocount on and set fmtonly off...
Have also set “DelayValidation” to True and "ValidateExternalMetadata” to False to assist the query times..
But SSIS seems to go off and be busy for 30 minutes without actually doing anything..we are sp 3 level....
We think that the bad performance is due to the Oracle database being at another site and linked using a 2 meg leased line..a tnsping gives a 200 msec response rate.
So
1) is there any tips to speed up the connection
2) any additional changes that need to be made to get round the no columns issue..
Did see some places mention creating a Table UDF and using that instead of a SP...
3) Should we have hit this issue in DTS?
Thoughts?
Regards
Ray
July 22, 2009 at 8:30 am
linked servers are notorious for slow performance; the reason is typically becuase the data is copied from the linked server into your servers tempdb, and then your operation/joins are performed...so dragging a SELECT * FROM MillionRowTable over the wire into tempdb takes time.
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply