SSIS pre-evaluation phase taking long

Question

I have a data flow that contains a OLEDB source (statement generated through a variable) which calls a stored procedure. In SSMS, it takes 8 minutes but the package itself takes 3 times longer to complete. I've set the validation (DelayValidation) to true, so it still does it at run time. Ive also set the validation of the metadata in the data flow component, as well as in the connection manager. The data flows have ReadUncommitted on them as well. I`m not sure where else to look, any assistance on how to make this run faster would be great.

Answer 1

I suspect the real problem is in your stored procedure, but I've included some basic SSIS items as well to try to fix your problem:

• Ensure connection managers for OLE DB sources are all set toDelayValidation ( = True).
• Ensure that ValidateExternalMetadata is set to false
• DefaultBufferMaxRows and DefaultBufferSize to correspond to the table's row sizes
• DROP and Recreate your destination component is SSIS
• Ensure your stored procedure has SET ANSI_NULLS ON
• Ensure that the SQL in your sproc hits an index

• Add the query hint OPTION (FAST 10000) - This hint means that it will choose a query which will optimise for the first 10,000 rows – the default SSIS buffer size

• Review your stored procedure SQL Server parameter sniffing.

Slow way:

create procedure GetOrderForCustomers(@CustID varchar(20))
as
begin
  select * from orders
  where customerid = @CustID
end

Fast way:

create procedure GetOrderForCustomersWithoutPS(@CustID varchar(20))
as
begin
  declare @LocCustID varchar(20)
  set @LocCustID = @CustID

  select * from orders
  where customerid = @LocCustID
end