ssis log has a long delay between onPostValidate and onpostexecuted

Sstyer 1 Reputation point
2022-04-11T18:39:40.777+00:00

I have an SSIS package that suddenly is taking a very long time, at certain times.
In the log
I'm seeing the OnPostValidate recorded say at 2am.
and the next event is the OnPostExecute is sometimes over 12 hours later.
Some times... it's within a minute of postvalidate.
which doesn't make any sense.

the update statement that's being performed is somewhat ugly

UPDATE transform.RateHistory SET transform.RateHistory.ToDate = ( SELECT TOP 1 innerH.FromDate FROM transform.RateHistory innerH WHERE innerH.RateName = transform.RateHistory.RateName AND innerH.HotelName = transform.RateHistory.HotelName AND innerH.FieldName = transform.RateHistory.FieldName AND innerH.FromDate > transform.RateHistory.FromDate AND SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( innerH.OriginalValue ,' ',''),CHAR(0),''),CHAR(1),''),CHAR(2),''),CHAR(3),''),CHAR(4),''),CHAR(5),''),CHAR(6),''),CHAR(7),''),CHAR(8),''),CHAR(9),''),CHAR(10),''),CHAR(11),''),CHAR(12),''),CHAR(13),''),CHAR(14),''),CHAR(15),''),CHAR(16),''),CHAR(17),''),CHAR(18),''),CHAR(19),''),CHAR(20),''),CHAR(21),''),CHAR(22),''),CHAR(23),''),CHAR(24),''),CHAR(25),''),CHAR(26),''),CHAR(27),''),CHAR(28),''),CHAR(29),''),CHAR(30),''),CHAR(31),''),1,500)

SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
transform.RateHistory.Value
,' ',''),CHAR(0),''),CHAR(1),''),CHAR(2),''),CHAR(3),''),CHAR(4),''),CHAR(5),''),CHAR(6),''),CHAR(7),''),CHAR(8),''),CHAR(9),''),CHAR(10),''),CHAR(11),''),CHAR(12),''),CHAR(13),''),CHAR(14),''),CHAR(15),''),CHAR(16),''),CHAR(17),''),CHAR(18),''),CHAR(19),''),CHAR(20),''),CHAR(21),''),CHAR(22),''),CHAR(23),''),CHAR(24),''),CHAR(25),''),CHAR(26),''),CHAR(27),''),CHAR(28),''),CHAR(29),''),CHAR(30),''),CHAR(31),''),1,500)
ORDER BY
innerH.FromDate,innerH.OriginalValue,innerH.Value
)
WHERE
transform.RateHistory.ToDate IS NULL
AND transform.RateHistory.RateName IN (SELECT RateName FROM transform.RateHistory WHERE FromDate > ?)

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,079 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 29,021 Reputation points
    2022-04-13T06:30:17.2+00:00

    which doesn't make any sense.

    It makes sense when the target table for the UPDATE is blocked by an other process; have you checked that?

    And there WHERE clause of your inner statement looks more the terrible. HAve you check the execution plan of your query and the with different parameter values?

    0 comments