This post is based off of the presentations that I have done for the SQL Saturday events in the past. If you have sat through one of these sessions it will probably be largely a repeat for you…
This post will look in depth at some of the more powerful aspects of Integrations Services: Expressions, Package Configurations, and Precedence Constraints.
(NOTE: Screen shots might have extra features visible which are included as part of the BIDS Helper plug-in)
Expressions are a combination of symbols that yield a single data value. They can be as simple as a single constant, variable or function (such as using the Logging File Name as the connection string) or as complex as joining multiple operators and functions while referencing multiple columns and variables.
Expressions are one of the more powerful features of SSIS, enabling users to easily customize a package by allowing many of its features to be dynamically evaluated at runtime. Expressions can be used in throughout SSIS, including properties on tasks, variables, connection managers and (some) components.
- Expressions often have a syntax like that of either "C" or "C#".
- This syntax uses CasESeNsitive variables
- Operators – || (OR), && (AND), != (Not Equal) , == (Equal), < (less than), <= (less than or equal), > (greater than), >= (greater than or equal)
- The ternary operator – (Boolean expression) ? (if true) : (if false).
- Literals are wrapped in double quotes " instead of single quotes ‘.
- Must use the namespace operator (::) to disambiguate variables with the same name. @[User::myVariable] vs. @[AuditVariable::myVariable]
- Within a literal the backslash is used to create an escape sequence. In other words, you would use \" to put a double quote within the literal or \\ to put in a single backslash.
- Lack of Case Statement makes multiple layers of If then Else End very messy…
Please note that not everything in the data flow has access to setting properties through expressions.
Expression Example 1 – Variables:
The following might be used to set a variable to ensure that the folder name contains a trailing slash. First, highlight your variable. Next, in the properties window, set the evaluate as expression to true. Finally, click on the expression and an ellipsis button (…) will appear. Click this to open the expression builder window.
The expression above reads:
RIGHT(@[User::LogFileFolder], 1) == "\\" ? @[User::LogFileFolder] : @[User::LogFileFolder] + "\\"
Expression Example 2 – Another Variable:
As a second example, here is a commonly requested item to append the date to the end of a file name. YYYYMMDDHHMISS format of course
The expression above reads:
@[User::LogFileFolder_Valid] + @[System::PackageName] + (DT_STR,4,1252)DATEPART( "yyyy" , @[System::StartTime] ) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime] ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System::StartTime] ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime] ), 2) +
NOTE: if you do not see the ellipsis when clicking on the expression property of the variable window, you might still be on a build of your client tools prior to SQL 2005 Service Pack 1. I would suggest you update.
Another Side Note: Most of my examples in other posts will include variables which contain a namespace other than user. It is often helpful to show the namespace for the variables in your variable list. To do so, click on the "Choose Variable Columns" button over the list of variables on the variables window. Then check the Namespace checkbox.
Expression Example 3 – Task Property:
Here we will set the log file connection string to use the file name we have built above. It is once again a simple procedure of highlighting the target object and going to the properties window. This time we will expand the expressions sub menu and click the ellipses to add a new property expression.
The expression above is pretty simple, it sets the connection string property and reads:
Package configurations are a mechanism for dynamically changing properties of your Integration Services objects and components at run-time using values that are stored externally to the package through a property / value pair. There are a number of methods to store and pass these values to a package:
- An XML file
- Environment variable
- Registry settings
- Configuration Table
- Parent Package Variable
As a matter of fact, you can string these together to form an "Indirect Configuration". I personally use an environment variable to set up my connection string that points to the configuration table on our server.
Most often Package Configurations are used to store information that will differ between environments. However, that is not all that they can do! Use them to determine a generic parameter, change date ranges, determine who receives an email for a particular run, etc. If used properly package configurations can be set up such that they are shared across your entire SSIS package catalogue.
Example Package Configurations:
Let’s take a look at where we can access the configurations and how we will add remove or edit these configurations.
First, to access the package configurations you will click on the SSIS menu and choose the package configurations from the menu. (Note: I have occasionally been experiencing an issue where I have to be on the control flow to get this menu to work right)
Here you will see the package configurations organizer. This list applies all of the configurations that this package will attempt to load at run-time. They will be applied from the top down in order of appearance (except for the parent package configurations). If a property is set in more than one configuration, the configuration which was set last will "win". Let’s choose the "Add…" option.
From here you are greeted by the first drop down box which determines the functionality on the rest of the home page.
If you select the xml configuration file you will be greeted with a screen similar to the following, wherein you will browse to or type in the name of the xml file that contains, or will contain, your configurations.
After hitting next you are asked to decide which properties will be included within this configuration file. You can choose as many or as few as you would like (Minimum one property).
Here is a sample xml file generated from the above:
If you were to instead choose the environment variable, a drop down list will be generated with all of the available choices:
You would choose the properties as above with the exception that you are allowed to choose one, and only one, property from the list for which to apply the configuration. The same restriction applies if you are using a Registry entry.
For a parent package variable, you must know the name of the variable which you will be retrieving. This package configuration type is worth noting in that, regardless of where it is at on the configuration list, they will be applied after all other configurations. Note that this configuration type will also only set a single property.
Finally, we have the choice of SQL Server configurations. Here we have a few more items to fill in before even getting to the "Select Properties to Export" dialogue. First off, we need to set a connection manager to reach the server. Then we need to choose the configurations table (or view). Finally we choose the configuration filter. This is a name that you would use to identify a logical grouping of properties. In the drop down list all current configuration filters will be available. Furthermore, you can type in the text box if you want to create another filter. Like XML Configurations, SQL Server Configurations allow multiple properties per configuration.
Here is a screen shot of the configurations table:
(NOTE: One of the do as I say, not as I do… particularly not as I do in a demo project. NEVER use mapped drives (i.e. C:) in production. Always used full UNC Paths…)
Package Configuration Side Notes:
One of my most referenced articles when either answering questions for co-workers or forum members has to be Jamie Thomson’s Indirect Configurations Rock!
Note that there are some changes in the way that configurations are loaded between 2005 and 2008. See John Welch’s post on Configuration Changes in SSIS 2008
When a number of the MVPs were asked to write an article relating to SSIS recently, without consulting each other, the majority of them decided to write on some aspect of configurations. Here is the index page for this series: Microsoft MVP Community Articles
Using the /Set Command:
Another topic that I continually mean to introduce into my presentation, but never seem to have time for, is the /Set Command. This can be used from the command line, the SQL Server Agent Jobs or the DTExecUtil to alter the value of nearly any property within the package that a package configuration could alter. It comes in handy when you do not have a package configuration set up but might need to run against some other environment (or perhaps import a file that is not named according to standards, etc).
Here is an example where you could change the Configurations connection string (so as to avoid the environment variable or point to a different environment altogether):
/Set \Package.Connections[Configurations].Properties[ConnectionString];"Data Source=localhost;Initial Catalog=Configurations;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;"
Precedence Constraints are used to control the workflow of a package (Between tasks). They consist of a combination of Expressions or constraints…
- Expression and Constraint
- Expression or Constraint
The Constraints Enumeration is used to determine what to do after a task depending on the exit condition of the task preceding it. The conditions available are success, failure and completion. When seen on the control flow these correspond to green, red and blue respectively.
When multiple precedence constraints lead into one task you can evaluate the set with either a logical "And" or a logical "OR". When shown on the control flow, the "OR" is shown with dotted lines while the "AND" is shown as solid lines. When an expression is part of the precedence constraint, a Fx symbol will be shown with the line.
In the sample package we want to show a few different examples of precedence constraints.
- Count Error Rows to know whether to delete the raw file.
- Check Error Rows to know whether to add information to the email sent upon process completion.
- Check to make sure that the trimmed error message is not a blank string before sending out email.
The following is the precedence constraint editor for point 2 above:
And here we show the sample workflow (This has some garbage tasks in it to show the different constraints):
In the example above the precedence between the dft and fst is either a "Expression and Constraint" or a "Expression or Constraint", same with between the dft and scr. The precedence between the scr and seq as well as the dft and seq is set up with a logical OR. The dft to seq has an expression with no constraint (or a constraint on completion, which is logically the same). The scr to seq has a constraint of success. Finally the dft to sql has a constraint of failure. In the OnPostExecute Handler we have the logic to send the email only if we have a message to send:
One thing to note about the above control flow is that the descriptions of the constraints are shown. To allow this, select the constraint you would like to work with, and, in the property window choose the ShowAnnotation drop down.
In the sample package I used the Reusable Variable Dispenser Functions from Daniel Read.
I modified the code John Welch’s Handling Multiple Errors In SSIS Packages so that it would use the above variable dispenser functions, as well as reporting on errors handled "gracefully".
Unfortunately you are not able to provide username and password, beyond the windows authentication, through the send mail task. As is, I did not have access to our work email server and thus am using Google Mail’s public facing SMTP Server via creating a script task to enable us to pass the credentials along. Credit to the posts of Jamie Thomson (Sending SMTP mail from the Script Task) and John Welch (using IS for email notification with gmail smtp) for pointing me in the correct direction for this one.
Well, I know that there are almost certainly better resources available on any of the subjects listed above. I was trying my best to condense the information down to a format which I could properly explain in about an hour’s worth of time. If you have comments or suggestions, please, feel free to leave them and I will do my best to respond in an appropriate fashion.
If anyone has an interest; the power point, package and some various create scripts are available as a zip on the sql saturday web site under the SQL Saturday Schedule for either the #8 or #10 Events. (Check that… it is only available through the #8 at the moment…)
Note that this package, database, configurations, etc are in no way shape or form reliable enough to be used in production. It was a toy produced to help explain some of the concepts discussed above. Use at your own risk with all the other usual safety warnings attached.