Posted by: Charles Maitland | Tuesday 7 March, 2006

SSIS Timeouts

One small annoyance that has just bitten me again is the time outs for SQL Server destinations.

By default it is 30 Secs. This does not appear to be the time out for a single write but for the entire operation.

The scenario I have is that I am doing an import into a data warehouse table of about 14 million rows. In the data flow is a lookup to a fiscal period dimension. If the lookup fails then the transaction is placed in an errors table. Now as some of them do fail (The client was not trading in the 1800′s!) there is data flow down this route. However, the error SQL Server Destination timed out because the main route did not complete within 30 seconds.

Not a major problem as if you set the timeout to 0 then it will wait for as long as is needed but  a pain when you have to re run the operation which in turn required about 10 mins of Pre – Execute phase.

About these ads

Responses

  1. Hello Charles,

    I have noticed that this is a post from 2006 and it has been almost 4 years now but the problem remains.

    I’m using SQL SSIS 2005 and I have a timout problem using a Execute Process Task to de-compact a file in order to load its data. The file is 2GB in size and SSIS does not wait for it to be fully de-compacted.

    I tried the ’0′ timeout value and even ’3600′ but with no success.

    Any ideas?

    Thanks in advance and your post has helped me notice the timeout issue, because I thought it was a file problem.

    Complements,

  2. Hi Charles,

    Thanks for your response and availability for a possible solution.

    It’s true that the SSIS takes a load on the memory in order to unpack the file but that was not the issue. The machine I am using is prepared for larger files and has permissions to access the necessary memory.

    I found out that all “parent” tasks had to have the time out at the same value or larger than “child” tasks. I had encapsulated my unpack task within another and the capsule (“parent”) had a time out of ’30′ seconds witch in turn was smaller than the ’3600′ of the “child” task resulting in a “parent” time out before the “child”‘s time out.

    Another point to take attention was the fact I had an expression in the Precedent Constraint (the arrow that connects the to tasks) following the task (@boolean = True) for validating the data flux. I did not include the ‘success’ validation, meaning that SSIS would ignore the time out value and continue with the flow.

    Solution was found in two steps:

    1. change the time out value in all tasks to ’0′ in order for SSIS to always waiting for the task to finish.

    2. include, when using expressions, the ‘success’ validation as well with ‘expression and constraint option’.

    Well that did the trick for me,

    Thanks

  3. If not, then you may want to start buying that latest video game
    console along with your favorite video games and start
    playing as research have found that playing with video games can indeed provide people with
    a great way to relax. If your daughter has more of an interest
    in dressing up than in cooking or she has a
    passion for both then go to dress up games. Just 2 minutes left which is enough time for a pretty productive offensive drive but not much else
    and everything has to go well.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: