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: 
Once the package is created and saved, open the package in SQL Studio: 
Once you have the package open we can begin to create Variables: You can create Variables from the Variables Pane (opened by pressing F4) 
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. 
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. 
Before we begin, you will need to ensure that you have the Properties window open: 
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: 
Now that we have the properties for the connection open click the … button to open the Expressions Dialog: 
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! 
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. 
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: 
Then click OK. The Expression should now be assigned to the Property: 
Click OK: Now when we assign a value to our Variable it will also be set to the connection manager property: 
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: 
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.