When creating an SSIS package it is always a best practice to use variables, and parameters in 2012,  to avoid hard coding values into any part of your package. But there are some best practices involved with creating those variables/parameters also. The rest of this article will refer to variables. But each one could be a parameter except for the variables with expressions. This is because an expression will over write any value passed in.

Let’s look at a common situation in an SSIS package. A common need is to save a file with a date appended to it. This can be done with an expression and a variable. The first example is going to be using one variable without the best practices applied.

Of course we are going to need a file system task to save the file for us. One of the nice features of the file system task is the fact that the rename function can rename and move a file. You simply set the source and destination to a different location. You should never need two file system tasks back to back, with one doing the move and the other doing the rename.

The first thing we are going to do is create a file connection in the connection manager of a package. The file location will be c:\test\test.txt. We are going to back this file up in the folder c:\test\backup and rename the file to test_YYYYMMDD.txt. This will give us today’s date at the end of each file we back up.

This could be a flat file connection also.

The source in the File system task will be set to this connection. We could use a variable to pass this name in if we needed. In this example the destination is the important part. The destination will use a variable so we set the property IsDestinationPathVariable to True.

We will need a variable on the package also. I will create a variable called strFileName. It is always a best practice to create variables with the first few letters of the name describing the data type and camel case the rest of the variable name. Here is a list of some data types and the extensions to use.

Now we set the properties of the variable. Set the property EvaluateAsExpression to true. Then click on the expression ellipse and set the expression to:

“c:\\test\\backup\\test”+ (DT_WSTR, 10) (DT_DBDATE) GETDATE() +”.txt”

Notice the double slashes. These are necessary due to escape characters. For example “\n” is new line. Also notie the double quotes around the literals. Then we simple use three functions. Getdate gives us the date, DT_DBDate trims the time from the date, and DT_WSTR converts the date to a string with 10 characters.

This is an easy way to save a file with the date. But this is not using best practices in SSIS. What if the folder you want to back the file up into changes? What if the file extension changes? Using configuration files or tables you can update these values from outside the package. But if the variable contains an expression then configuration file value would be over written when the expression is evaluated. Therefore it is necessary and a best practice to use multiple variables.

Now I will show the same example using variable best practices in SSIS. We are going to keep the same connection in the connection manager and the source in the file system task will still be set to this connection. But instead of creating one variable for the backup file location we will create several variables. Here they are:

strBackupFolder – The backup location

strFileName – The name of the file without the folder or extension

strFileExtension – The extension of the file

strFullFileName – All the other variables combine in this one

strDate – The date represented as a string

strBackupFolder will be set to “c:\test\backup\”

strFileName will be set to “test”

strFileExtension will be set to “.txt”

strFullFileName will be set to an expression

strDate will be set to an expression

Now for the two expressions, the strDate will be just like the expression from the previous example. We use the same three functions to get a date string: (DT_WSTR, 10) (DT_DBDATE) GETDATE(). If you want the date to be in a different format then you will need to use the function Year, Day, and Month to control the format of the date.

The strFullFileName will simply combine all the other variables together to form a complete file name.

Now each of the variables that do not contain expressions can be updated from outside the package with configuration file or configuration tables and they still play nicely with the expressions in the other variables. In any packages you create try to ask the questions like “What if something changes?” This will save you tons of headaches in the future from maintenance. Always try to combine multiple variables with expressions, do not code everything into one variable.