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

Suggested Best Practises and naming conventions

Once upon a time I blogged at but that ended in August 2009 when I left EMC. There is a lot of (arguably) valuable content over there however certain events in the past leave me concerned that that content is not well cared for and I don't have any confidence that it will still exist in the long term. Hence, I have taken the decision to re-publish some of that content here at SQLBlog so over the coming weeks and months you may find re-published content popping up here from time-to-time.

This is the third such blog post in which I suggest some best practises and naming conventions that you may choose to employ and which was originally published here (I have changed it slightly since then – spotters badge if you can find the differences!). The first post in this series can be found at [SSIS] OnPipelineRowsSent and the second at Dataflow mechanics.

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices. These are my own opinions and are based upon my experience of using SSIS over the past 18 months. I am not saying you should take them as gospel but these are generally tried and tested methods and if nothing else should serve as a basis for you developing your own SSIS best practices.

One thing I really would like to see getting adopted is a common naming convention for tasks and components and to that end I have published some suggestions at the bottom of this post.

This list will get added to over time so if you find this useful keep checking back here to see updates!

  1. If you know that data in a source is sorted, set IsSorted=TRUE on the source adapter output. This may save unnecessary SORTs later in the pipeline which can be expensive. Setting this value does not perform a sort operation, it only indicates that the data it sorted.
  2. Rename all Name and Description properties from the default. This will help when debugging particularly if the person doing the debugging is not the person that built the package.
  3. Only select columns that you need in the pipeline to reduce buffer size and reduce OnWarning events at execution time
  4. Following on from the previous bullet point, always use a SQL statement in an OLE DB Source component or LOOKUP component rather than just selecting a table. Selecting a table is akin to "SELECT *..." which is universally recognised as bad practice. ( In certain scenarios the approach of using a SQL statement can result in much improved performance as well (
  5. Use SQL Server Destination as opposed to OLE DB Destination where possible for quicker insertions I used to recommend using SQL Server Destinations wherever possible but I've changed my mind. Experience from around the community suggests that the difference in performance between SQL Server Destination and OLE DB Destination is negligible and hence, given the flexibility of packages that use OLE DB Destinations it may be better to go for the latter. Its an "it depends" consideration so you should consider what you prefer based on your own testing.
  6. Use Sequence containers to organise package structure into logical units of work. This makes it easier to identify what the package does and also helps to control distributed transactions if they are being implemented.
  7. Where possible, use expressions on the SQLStatementType property of the Execute SQL Task instead of parameterised SQL statements. This removes ambiguity when different OLE DB providers are being used. It is also easier. (UPDATE: There is a caveat here. Results of expressions are limited to 4000 characters so be wary of this if using expressions).
  8. Use caching in your LOOKUP components where possible. It makes them quicker. Watch that you are not grabbing too many resources when you do this though.
  9. LOOKUP components will generally work quicker than MERGE JOIN components where the 2 can be used for the same task ( Note that this is not always the case so test and measure, test and measure, test and measure!
  10. Always use DTExec to perf test your packages. This is not the same as executing without debugging from SSIS Designer (
  11. Use naming conventions for your tasks and components. I suggest using acronyms at the start of the name and there are some suggestions for these acronyms at the end of this article. This approach does not help a great deal at design-time where the tasks and components are easily identifiable but can be invaluable at debug-time and run-time.  e.g. My suggested acronym for a Data Flow Task is DFT so the name of a data flow task that populates a table called MyTable could be "DFT Load MyTable".
  12. If you want to conditionally execute a task at runtime use expressions on your precedence constraints. Do not use an expression on the "Disable" property of the task.
  13. Don't pull all configurations into a single XML configuration file. Instead, put each configuration into a seperate XML configuration file. This is a more modular approach and means that configuration files can be reused by different packages more easily.
  14. If you need a dynamic SQL statement in an OLE DB Source component, set AccessMode="SQL Command from variable" and build the SQL statement in a variable that has EvaluateAsExpression=TRUE. (
  15. When using checkpoints, use an expression to populate the CheckpointFilename property which will allow you to include the value returned from System::PackageName in the checkpoint filename. This will allow you to easily identify which package a checkpoint file is to be used by.
  16. When using raw files and your Raw File Source Component and Raw File Destination Component are in the same package, configure your Raw File Source and Raw File Destination to get the name of the raw file from a variable. This will avoid hardcoding the name of the raw file into the two seperate components and running the risk that one may change and not the other.
  17. Variables that contain the name of a raw file should be set using an expression. This will allow you to include the value returned from System::PackageName in the raw file name. This will allow you to easily identify which package a raw file is to be used by. N.B. This approach will only work if the Raw File Source Component and Raw File Destination Component are in the same package.
  18. Use a common folder structure (
  19. Use variables to store your expressions ( This allows them to be shared by different objects and also means you can view the values in them at debug-time using the Watch window.
  20. Keep your packages in the dark ( In summary, this means that you should make your packages location unaware. This makes it easier to move them across environments.
  21. If you can, filter your data in the Source Adapter rather than filter the data using a Conditional Split transform component. This will make your data flow perform quicker.
  22. When storing information about an OLE DB Connection Manager in a configuration, don't store the individual properties such as Initial Catalog, Username, Password etc... just store the ConnectionString property.
  23. Your variables should only be scoped to the containers in which they are used. Do not scope all your variables to the package container if they don't need to be.
  24. Employ namespaces for your packages
  25. Make log file names dynamic so that you get a new logfile for each execution.
  26. Use ProtectionLevel=DontSaveSensitive. Other developers will not be restricted from opening your packages and you will be forced to use configurations (which is another recommended best practice)
  27. Use annotations wherever possible. At the very least each data-flow should contain an annotation.
  28. Always log to a text file, even if you are logging elsewhere as well. Logging to a text file has less reliance on external factors and is therefore most likely to contain all information required for debugging.
  29. Create a new solution folder in Visual Studio Solution Explorer in order to store your configuration files. Or, store them in the 'miscellaneous files' section of a project.
  30. Always use template packages to standardise on logging, event handling and configuration.
  31. If your template package contains variables put them in a dedicated namespace called "template" in order to differentiate them from variables that are added later.
  32. Break out all tasks requiring the Jet engine (Excel or Access data sources) into their own packages that do nothing but that data flow task. Load the data into Staging tables if necessary. This will ensure that solutions can be migrated to 64bit with no rework.
  33. Don't include connection-specific info (such as server names, database names or file locations) in the names of your connection managers. For example, "OrderHistorySystem" is a better name than "Svr123ABC\OrderHist.dbo".

The acronyms below can be used at the beginning of the names of tasks to identify what type of task it is.



For Loop Container


Foreach Loop Container


Sequence Container


ActiveX Script


Analysis Services Execute DDL


Analysis Services Processing


Bulk Insert


Data Flow


Data Mining Query


Execute DTS 2000 Package


Execute Package


Execute Process


Execute SQL


File System




Message Queue




Send Mail


Transfer Database


Transfer Error Messages


Transfer Jobs


Transfer Logins


Transfer Master Stored Procedures


Transfer SQL Server Objects


Web Service


WMI Data Reader


WMI Event Watcher







These acronyms should be used at the beginning of the names of components to identify what type of component it is.




DataReader Source


Excel Source


Flat File Source


OLE DB Source


Raw File Source


XML Source






Character Map


Conditional Split


Copy Column


Data Conversion


Data Mining Query


Derived Column


Export Column


Fuzzy Grouping


Fuzzy Lookup


Import Column






Merge Join




OLE DB Command


Percentage Sampling




Row Count


Row Sampling


Script Component


Slowly Changing Dimension




Term Extraction


Term Lookup


Union All




Data Mining Model Training


DataReader Destination


Dimension Processing


Excel Destination


Flat File Destination


OLE DB Destination


Partition Processing


Raw File Destination


Recordset Destination


SQL Server Destination


SQL Server Mobile Destination


Published Sunday, January 29, 2012 7:08 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



RichB said:

Wow, Hungarian notation again.  Really?

February 1, 2012 5:48 AM

jamiet said:


Sorry, not sure I understand. Where have I advocated hungarian notation? Are you referring to the use of prefixes on naming conventions?


February 1, 2012 5:57 AM

Guido said:


SQL Best Practices and Standards is my favorite topic. I am always looking for new ideas to add to my SQL Formatter tool (it is called SQLinForm) and was surprised that you had no suggestions regarding to the formatting of SQL statements. Does it mean that you do not do find it important to write SQL statements in a nice and readable way?



February 7, 2012 11:44 AM

jamiet said:

No, it means that the scope of this blog post does not include formatting of SQL statements. That's why the first sentance is:

"I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices"

and not

"I thought it would be worth publishing a list of guidelines that I see as T-SQL development best practices"

February 7, 2012 11:48 AM

Guido said:

Ok. I see. Thanks. Whenever you will publish your ideas related to T-SQL formatting standards, I would be very interested.



February 7, 2012 12:18 PM

jamiet said:

Will do. I do plan to cover some of that at some point actually! Stay tuned.

February 7, 2012 12:21 PM

Koen Verbeeck said:

Ah, the bible for every SSIS developer. Good thing you chose to preserve this blog post. I still use it in all my trainings as a reference, especially the naming conventions for task/components.

February 9, 2012 5:47 AM

jamiet said:

"The bible". Wow :)

February 9, 2012 5:54 AM

SSIS Junkie said:

My mind was wandering today after reading Andy Leonard's excellent post Name Those Connections and it

February 9, 2012 9:10 AM

Jamie Thomson said:

A long long long time ago (in 2006 in fact) I published a blog post entitled Suggested Best Practises

October 10, 2013 4:10 AM

Steven Neumersky said:


These are ALMOST the same as the standards I a tee!

February 21, 2014 5:29 PM

MAGESH said:

thank you so much. this helped me.

August 14, 2014 8:40 AM

rudeb0y said:

I think you should add - "Use the BIDS Helper add-in" as a standard!

I tell this to all my colleagues now and they've been very slow to adapt. However, once it's tried no-one ever un-tries it. Even just opening up a package you did not develop and instantly know where an expression has been used is a godsend. IMO if you are not using BIDS helper, you are doing it wrong!

September 30, 2014 11:22 AM

PMah said:

Just a note to say thanks for this list of best practices. But also, I found that the Cache Transformation is missing from your table! Suggestion: CTR. I'm not sure whether any others are missing.

Thanks again! :)

September 16, 2015 5:00 AM

JohnN said:

Accessing on 21-Jan-2016 the blog posts referenced at seem to have gone :-/

Are there copies of these posts anywhere?

January 21, 2016 11:06 AM

jamiet said:

Hi John,

Unfortunately the blogs at have been shut down. Conchango (my ex employer) got bought by a huge multinational who apparently didn't see any value in keeping valuable content alive. You can imagine my anger.

Unfortunately the only place to see them, now is at the wayback machine:

January 21, 2016 4:27 PM

JohnN said:

Thanks for the link to the wayback machine.  It looks like some of the really old blogs have gone to the big data archive in the sky.  Ashes to ashes, bytes to bytes.

The particular entry I was trying to find was from November 2006 about using namespaces in package names.  In the brave new world of SSISDB I'm wondering if that advice still applies?

March 16, 2016 1:09 PM

Senthl J said:

Regarding #7, I would go for Stored Procs (even if they are parameterized) as opposed to inline SQL Statements. Reason being, it doesn't have any smarts to complains if there are any errors in your SQL Statements. Also when you want to change the SQL Statement down the track, you would have to open the package and make the changes. It it is a Stored Proc, you don't have the above issues.

April 20, 2016 2:13 AM

jamiet said:

Hi Senthl J,

Thanks for the comment.

#7: "Where possible, use expressions on the SQLStatementType property of the Execute SQL Task instead of parameterised SQL statements. This removes ambiguity when different OLE DB providers are being used. It is also easier. (UPDATE: There is a caveat here. Results of expressions are limited to 4000 characters so be wary of this if using expressions)."

I never mentioned stored procedures or inline SQL statements in the above statement and I definitely did not compare them. if you want to call stored procedures in your Execute SQL Task go right ahead, nothing I said in that bullet point says anything to the contrart (IMO).



April 20, 2016 4:03 AM

JohnC said:

Hi Jamie,

Awesome list, I've been back here a few times. I thought I saw it here, but couldn't find it in the list this time. What's your opinion on having separate SOURCE and DESTINATION connections to the SAME database? Is there a technical advantage in doing so?

Thank you

October 12, 2016 2:45 PM

Leave a Comment


This Blog


Privacy Statement