[CranPort / Assemble] Solutions for Package Timeout

If a CranPort / Assemble package, created from Collect / DataGarage is timing out, the following steps should resolve the issue.  Of course, timing out does suggest a possible performance issue.  To increase the performance of a package, check out articles like Reducing Table Download Times (while some articles like this one reference only DataGarage, the information remains accurate for Collect).

1. Update the default timeout in CranPort / Assemble Parameters (CranPort.dbo.CPParam.Timeout) to 1200 or greater. This will affect all packages that have a NULL Timeout setting on their vertical.
   • example query:  UPDATE CranPort.dbo.CPParam SET [Timeout] = 1200
2. Update the specific [Time Out] setting on the {vertical} -> {Advanced Properties} tab of the CranPort / Assemble package that is timing out.  Increase it as high as 3600 or 7200 if necessary.
   • If you still get timeouts, you can try lowering the [Batch Size] (to 5000 or 1000).
3. Update DataSource connection and command timeouts. Make sure absolutely none are NULL or 0. For the ones related to the packages that are failing, increase the Command Timeout to anything from 600 on up until the package works.
   
   • example query that sets all DataSource timeouts to at least 60 and 600 (and leaves higher values alone):

   • UPDATE CranSoft.dbo.DataSource
     SET CommandTimeout = 600
     WHERE [Database] IS NOT NULL
     AND ISNULL(CommandTimeout, 30) < 600
     
     UPDATE CranSoft.dbo.DataSource
     SET ConnectionTimeout = 60
     WHERE [Database] IS NOT NULL
     AND ISNULL(ConnectionTimeout, 15) < 60
     
     UPDATE DSPCommon.dbo.ttDataSourceRegistry
     SET CommandTimeout = 600
     WHERE [Database] IS NOT NULL
     AND ISNULL(CommandTimeout, 30) < 600
     
     UPDATE DSPCommon.dbo.ttDataSourceRegistry
     SET ConnectionTimeout = 60
     WHERE [Database] IS NOT NULL
     AND ISNULL(ConnectionTimeout, 15) < 60