SSIS Package - User Variables
A SSIS (SQL Server Integration Services) package is very useful if you want to export data from your database. Recently I wanted to pass some input parameters to my SSIS package and had to struggle to figure out how this can be done. This really isn't very well documented and you can't find detailed instructions on the Internet so I thought I'd blog about it. Get a load of how complicated it turned out to be!
The first step is to add a variable to your SSIS package. In the Business Intelligence Development Studio (Visual Studio 2005), select SSIS > Variables
The first button on the left is for Add Variable. Enter the variable name, scope, data type, and value. The variables will be added to the DTSX file:
1: <DTS:Variable>
2: <DTS:Property DTS:Name="Expression"></DTS:Property>
3: <DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property>
4: <DTS:Property DTS:Name="Namespace">User</DTS:Property>
5: <DTS:Property DTS:Name="ReadOnly">0</DTS:Property>
6: <DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property>
7: <DTS:VariableValue DTS:DataType="7">1/1/1950</DTS:VariableValue>
8: <DTS:Property DTS:Name="ObjectName">varBirthDate</DTS:Property>
9: <DTS:Property DTS:Name="DTSID">{6CE1ED2D-3720-4FDB-9590-FEDC2D78C797}</DTS:Property>
10: <DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property>
11: </DTS:Variable>
The next step is to map a parameter in your SQL statement to the variable.
- Select the Data Flow tab.
- Right click on the Source Query
- Select Edit
- Replace a hard coded value in the SQL statement with a question mark to create a parameter
- Click the Parameters... button
- Select the variable from the drop down list
- Enter the parameter name in the format @Name for a stored procedure parameter or the field name
The third step is to add this variable to the package configuration so you can easily change the value in an XML configuration file.
- Select SSIS > Package Configurations
- Click the Edit button and then the Next button
- In the Select Properties to Export dialog box, check the Value property of the variable (which will not appear unless you complete the previous steps to create it first).
The variables will be added to the .dtsConfig file:
1: <Configuration ConfiguredType="Property" Path="\Package.Variables[User::varBirthDate].Properties[Value]" ValueType="DateTime">
2: <ConfiguredValue>1/1/1950</ConfiguredValue>
3: </Configuration>
The SSIS package can be run by an ASP.NET page or a web service by importing the Microsoft.SqlServer.Dts.Runtime namespace. The input parameter can easily by changed in the package configuration file by loading it as an XML document and editing the appropriate node.
You can use SSIS packages to do very complicated data transformations and exports. Basically it automates everything the SQL Server Import and Export Wizard does. So you can export your database to Access or Excel without repeating that whole process of going through the wizard steps. I've found this particularly useful to set up my own scheduled back up of a remote SQL Server database used by a hosted web application.