PBM for SSIS? What policies would you want?

Hi Jamie,

  Required parameters?

:{>

Oh, and DelayValidation settings for Data Flows and Connection Managers...

+1 for the "Delay Validation" settings. Been hit by that far too many times. Perhaps something along the lines of the OLEDB destination settings would be worth considering - not using defaults or deviations from some standard raise a warning.

Along those lines, some sort of warning on excessive blocking transforms might be worth considering as well (e.g. Sort).

Agreed that Andy's reminder was very timely on setting that Application in SSIS (and elsewhere).

Hi, Jamie - RetainSameConnection for connection managers must be set to true, unless there is a good reason for not doing so.

There is a package with a number of execute sql tasks that execute sequentally; one for every dimension and fact. All these have the same OLEDB connection. By default when retainsameconnection is set to false, a new connection is opened for each task and closed after execution. But, when retainsameconnection is set to true connection

to OLEDB source is opened only once and all the tasks use the same connection. Not sure how much of a performance gain this is, but eliminates the unwanted process of opening and closing connection.

I'm not a big fan of using temp tables, but because of this default setting, you will not be able to use a temp table in task2 that was created in task1.

Thanks,

Samuel.

Hi Jamie,

This is a nice idea - I totally support your initiative.  Here are my ideas:

Ensure standardised Transactions properties (IsolationLevel & TransactionOption) across Packages - Containers - Tasks.

Warn if OLE DB Destination for SQL is not using Fast Load & Table Lock.

Thanks

Mike

Hi Jamie, i hit the submit button little early on my previous comment.

Thank you for asking my thoughts.

I would like the ability to post some manner of warning when developers use tasks like the OLD DB Command, which are known to perform poorly.