Passing Published Variables from Orchestrator TO SSIS (DTSX) Packages

Author by Lee Berg

During the course of various SCSM integration projects, we have the need to import data from external data sources such as CSV files or External Databases from other systems (Inventory Systems, Remedy Service Desk, etc.) and place them in seperate SQL Database table for further processing. We have primarily utilized SQL Server Integration Services (SSIS) to create DTSX packages to serve this purpose. TechNet describes the SSIS packages as: “A package is an organized collection of connections, control flow elements, data flow elements, event handlers, variables, parameters, and configurations, that you assemble using either the graphical design tools that SQL Server Integration Services provides, or build programmatically. You then save the completed package to SQL Server, the SSIS Package Store, or the file system. The package is the unit of work that is retrieved, executed, and saved.” Read more on SSIS Packages here: http://technet.microsoft.com/en-us/library/ms141134.aspx We typically save packages to a file store as a “DTSX” file extension so they can be easily accessed and shared between runbooks. These SSIS DTSX packages are executed with the DTEXEC.exe command line utility. If you happen to work with DTSX packages, you will quickly find that although they are a great tool they can be very difficult to work with. It is often quite difficult to change the properties of a DTSX without having to rebuild the package or at the very least, having to spend a great deal of time to make many complex changes to the DTSX package settings. We have made use of a method to utilze System Center Orchestrator to run our DTSX packages to elimante these problems. To get straight to the point, we are going to use Orchestrator to pass Published Variables into the DTSX package itself. This way we can dynamically change the behavior of our DTSX package by providing it with variables that we can assign through Orchestrator Logic. By designing your DTSX packages in this manner, the packages will NOT have to be re-created / modified when moving to new environments, or circumstances where major DTSX settings need to be adjusted. In some cases, this can allow a single DTSX can be used for multiple purposes. What follows is a tutorial on how to setup your DTSX packages to use variables The first step is to create your DTSX as you normally would using the SQL Server Import and Export Wizard: clip_image002 clip_image004 Once the package is created and saved, open the package in SQL Studio: clip_image005 Once you have the package open we can begin to create Variables: You can create Variables from the Variables Pane (opened by pressing F4) clip_image007 Once you have opened the pane, you can simply click the New Variable button to create a new variable. These variables that we are creating here are examples that you can use in your packages. I would recommend that you name your variables with a logical convention. We will be specifically assigning values to these variables later on in an Orchestrator script. clip_image009 Here is a quick explanation of the various Variable Settings: SCOPE: In this case the “SCOPE” is “ImportCSV” which in this case is the name of the DTSX. You can scope your variables to specific activities in your DTSX package but if you are not using a huge number of variables you can just as easily scope your variables to the entire DTSX. Data Type: In most cases your Variables will function just fine as Data Type String, String works fine for activity properties and in Queries. Value I like to think of the “Value” column as the “default value” of thee variable. Specifying the Value in command line using Orchestrator will overwrite whatever value you enter here in the variables pane Now that we have our variables we can apply these variables to the various properties in our DTSX. As an example we will assign our variables to our Destination and Source locations through the Connection Managers. This is a great use case as it allows us to dynamically assign where our DTSX is pulling its info from, and where it will be sent. clip_image010 Before we begin, you will need to ensure that you have the Properties window open: clip_image012 Your workspace should now have the properties pane, click on the Connection in the Connection Manager Pane that you wish to assign properties to Variables: clip_image014 Now that we have the properties for the connection open click the … button to open the Expressions Dialog: clip_image016 This is where we can assign our Variables to the properties of the Connection Manager object: Select a Property from the Property Dropdown. Example here is “InitialCatalog’ , Also note ServerName, UserName, etc. These are all values that usually have to change between environments that can be assigned as variables! clip_image017 Once you have selected the property you wish to set with a variable, click the Expression … button to open the Expression dialog, this allows us to assign our Variable to the connection property. clip_image018 Expand the “Variables” folder in the upper left pane. You can now select / combine / eval / etc. variables into an expression. In this case we can simply select our User variable: USER:DestinationCatalog, then drag and drop the variable to the Expression Pane: clip_image020 Then click OK. The Expression should now be assigned to the Property: clip_image021 Click OK: Now when we assign a value to our Variable it will also be set to the connection manager property: clip_image023 Once you save your DTSX package changes you can now Set the variable in the command line from Orchestrator when we call our DTSX You set Package variables with the DTEXEC.exe utility with the following parameter:

/Set '\Package.Variables[User::VariableNameHere].Properties[Value];ValueGoesHere'

We can then use a PowerShell Run .Net Activity in Orchestrator to call the DTSX:

& 'D:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe' /FILE "\\SERVERNAME\D$\download\ImportCSV.dtsx" /Set '\Package.Variables[User::VariableNameHere].Properties[Value];ValueGoesHere' /REPORTING EW

And then we can replace our static variable name with an Orchestrator subscription: clip_image025 Now we are “Dynamically” assigning connection values in our DTSX. Using this method we can eliminate the need to recreate DTSX between Environment moves and can save you tons of time where DTSX packages are required.

Author

Lee Berg

Systems Engineer