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.