THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

PBM for SSIS? What policies would you want?

My mind was wandering today after reading Andy Leonard's excellent post Name Those Connections and it occurred to me that due to SQL Server 2012 including an SSIS Server there may be an opportunity to leverage Policy-Based Management (PBM) for ensuring adherance to an organisation's best practises around SSIS development. I have absolutely no idea whether such plans are afoot or not (I suspect not) but nonetheless it may be fun to come up with a desired list of policies. Here are some brainstormed ideas:

  • All task and package names have to adhere to some naming convention (here are some suggestions)
  • All executions need to use an environment reference
  • All Connection Managers must be project-scoped
  • All connection strings must use an Application Name that is not a GUID (this is where this train of thought started after reading Andy's post)
  • Insist on a given LOGGING_LEVEL

Any more ideas? Pop 'em in the comments below!


Published Thursday, February 9, 2012 2:50 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



andyleonard said:

Hi Jamie,

  Required parameters?


February 9, 2012 9:13 AM

andyleonard said:

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

February 9, 2012 9:14 AM

jamiet said:

Reuired params. Absolutely, yes. Good one Andy.

February 9, 2012 9:37 AM

Peter Schott said:

+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).

February 9, 2012 9:43 AM

Samuel Vanga said:

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

February 9, 2012 11:29 AM

jamiet said:

Hi Samuel,

Why do you think it *should* be set to true? Not saying it shouldn't...jsut interested to know your thoughts that's all.

February 9, 2012 11:44 AM

Samuel Vanga said:

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.



February 9, 2012 1:01 PM

Mike Honey said:

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.



February 9, 2012 6:10 PM

Samuel Vanga said:

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

Thank you for asking my thoughts.

February 10, 2012 9:20 AM

MarkVSQL said:

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.

February 10, 2012 11:22 AM

Leave a Comment


This Blog


Privacy Statement