Sign in Subscribe

SSIS: Use those Variables!

Arie D. Jones

• 3 min read

    One of the most understated objects that can be used within SSIS is the variable. Variables are accessable through the variables pane within Business Intelligence Development Studio(BIDS). This pane will show you the various variable names, their scope, and also the current value of the variable as shown in the screen shot below.

    All of this is pretty rudimentary. You create variables and then assign them values. However, more often than not we would like our routines to be as adaptable as possible and require as little administration as possible. So it would be advantageous to us if we could create dynamic variables that would be evaluated at runtime based upon a set of base variables. Luckily, SSIS enables us to do this quite easily with little if no coding at all required on our part. If you look back to my post Working with SSIS Part #1 , you will see that we use the For Each looping object to loop through a set of Excel files in a directory. I briefly mention that it would be nice to have the files archived after processing in order to keep your working directory clean of file clutter.
    We could preform this action rather easily by using a dynamic variable. First we would want to change out For Each loop to only grab the ‘Name and Extension’ of our files. Now we set up 2 variables

  • strCurrentFileDirectory: Location of the directory of excel files for the For Each Loop
  • strArchiveFileDirectory: Location of the directory for the archive files 

    This will allow us to make a more configurable package as we can update these 2 variables to customize the location of where the excel files to be read are and where we would like these files to be copied to. Next we create two more variables

  • strArchiveFilePath: this variable will contain the full path to the archived version of the file
  • strCurrentFilePath: this will contain the full path to the current file in the For Each loop.

    If you are working with the same project as we created in the previous post linked above then you already have a variable named strCurrentFile. You now need to set the strCurrentFilePath variable so that it will always point to the fully qualified path of the current file in the For Each Loop. To do this open the properties window and then click on the strCurrentFilePath variable in the variables window in order to display it s properties. Now change the EvaluateasExpression property to True. This will tell SSIS that you want it to use the evaluation statement to determine what value the variable holds. Now click on the … button next to the Expression property to open the expression dialog box and type in or drag and drop the following expression into the text window.
@[User::strCurrentFileDirectory] +  @[User::strCurrentFile]

    This will now always evaluate this statement and should always be the full path to the current file in the For Each loop. Do the same thing for the strArchiveFilePath except use the following expression.
@[User::strArchiveFileDirectory] +  @[User::strCurrentFile]

    Next, change the expression in your excel connection object to read the strCurrentFilePath for the current working file and change the File task object to use your two dynamic variables for the source and destination locations as shown below for the Move File task.

    And you are done. Now when the locations of your files change you can just change the directory variables and the rest of the SSIS process should take care of itself saving you valuable amounts of time and energy.

Technorati Tags: , , , , , , , , ,

Sign up for more like this.

Enter your email
Subscribe