Create a UI for changing SSIS package variables and input parameters
| Executive Summary:|
If you're executing SQL Server Integration Services (SSIS) packages in different environments, such as development, QA, or production, you need a set of configuration files or scripts to generate SQL Server jobs that let you execute packages with various input parameters and configuration specs. This solution provides a UI through which end users can make such changes, by calling a Windows Form from an SSIS package, coded using Visual Studio 2005 and SQL Server 2005 Development Studio (BIDS).
As I've developed SQL Server Integration Services (SSIS) packages, I've pondered how to make those packages easier to use. If you're executing SSIS packages in different environments—such as development, integrated testing, QA, staging, and production—you need a set of configuration files or scripts for generating SQL Server jobs that would enable you to execute packages with different input parameters and make some manual configuration changes. In this case, organizing a simple regression-testing process could be really painful.
I found a way to resolve this issue using Windows Forms. By using a Windows Form that's called from package, user can provide the most important package input parameters (e.g., date ranges, execution mode—initial upload or incremental, server name, database name) dynamically during the package execution. In an example usage scenario for this technique, the parent package calls the Windows Form and child package in a loop, and the child package is executed with different input parameters provided by the user through the Windows Form. I'll show you how to perform a Windows Form call from an SSIS package so that you can similarly provide a UI for setting package variables and changing parameters.
My example uses a simple Windows Form and SSIS package that I developed. The code examples I provide were developed using Visual Studio 2005 and SQL Server 2005 Business Intelligence Development Studio (BIDS). I tested the solution in a Windows XP and SQL Server 2005 Developer Edition environment.
Step 1: Develop the Windows Form
To develop the Windows Form, you need to create a new project by selecting Visual Basic, Windows project type and using the Windows Application template. The Select_Server_Form.zip file contains the solution for developed Windows form. You can download this file by clicking the Download the Code link at the top of this article.
The form is very simple: It has one text box control in which the user can type in information and two buttons, Continue and Exit. When Continue is clicked, the text box value is assigned to the _ServerName form property to be consumed later by the package. When Exit is clicked, package execution is terminated. Listing 1 shows the sample code for the Windows Form.
Listing 1: Code for Windows Form
Public Class Form1<p> Private Server_Name As String<br> Private Action As String</p><p> Public Property _ServerName() As String<br> Get<br> Return Server_Name<br> End Get<br> Set(ByVal Value As String)<br> Server_Name = Value<br> End Set<br> End Property</p><p> Public Property _Action() As String<br> Get<br> Return Action<br> End Get<br> Set(ByVal Value As String)<br> Action = Value<br> End Set<br> End Property</p><p> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)<br> Handles Button1.Click<br> Me.Action = "Continue"<br> Me.Server_Name = Me.TextBox1.Text<br> Me.Close()<br> End Sub</p><p> Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)<br> Handles MyBase.Load<br> Me.TextBox1.Text = Me.Server_Name<br> End Sub</p><p> Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)<br> Handles Button2.Click<br> Me.Action = "Exit"<br> Me.Server_Name = "N/A"<br> Me.Close()<br> End Sub</p><p>End Class</p>
Unfortunately, Visual Basic is your only language choice if you will use the developed code in a SQL 2005 SSIS Script Task component. For, you could use C# as an alternative, but I didn't have an opportunity to test this case.
Step 2: Develop the Package with Script Task Components
Now we need to create the SSIS package with the Script Task component to perform the call to the Windows Form developed in step 1. The WinForm_from_SSIS.zip file, which you can download at the top of the page, contains the sample package I developed. Figure 1 shows the structure of the package I developed.
Figure 1: SSIS package structure
The SSIS package has two string variables—\[Action\] and \[Server_Name\]—defined at the package-level scope with the initial value N/A and four control-flow components. Three Script Task components—Before, Call Windows Form, and After—are placed in Run it in Loop For Loop container. The Exit condition for the Run it in Loop container is defined as
@\[User::Action\] != "Exit"
The \[Before\] and \[After\] Script Task components have the same functionality: Each reads the package variables and shows their values in a Message Box. Listing 2 shows the code for the \[Before\] Script Task component.
Listing 2: Code for \[Before\] Script Task component
Imports System<br>Imports System.Data<br>Imports System.Math<br>Imports Microsoft.SqlServer.Dts.Runtime<p>Public Class ScriptMain</p><p> Public Sub Main()<br> '<br> System.Windows.Forms.MessageBox.Show("Package variables values before<br> Windows Form call: " & vbCrLf _<br> & "Server_Name: " & Dts.Variables("Server_Name").Value.ToString & vbCrLf _<br> & "User Action: " & Dts.Variables("Action").Value.ToString & vbCrLf,<br> "Package Variables: BEFORE")<br> Dts.TaskResult = Dts.Results.Success<br> End Sub</p><p>End Class</p>
Now we need to add two classes with Windows Form code to the ScriptTask project in the \[Call Windows Form\] Script Task component. To do so, open the Script Task component for editing and select the Script option in the left pane. Click Design Script, click the ScriptTask project, and press the right mouse button to display the context menu. Explore the Add item, select the Add Class option, enter the class name in the opened dialog box, and click the Add button.
In my case I created two classes, Form1 and Class1. The Form1 class contains form-related code from Listing 1. Class1 contains partial class-definition code from the VB Windows Forms solution that was created at the beginning of this article. Next locate the Form1.vb and Form1.Designer.vb files in that project. Open those files and copy and paste the code from Form1.vb to the Form1 class and from Form1.Designer.vb to the Class1 class.
Next we'll add code for the Windows Form call to the ScriptMain class in the ScriptTask project; Listing 3 shows this code. Save the changes and close the project.
Listing 3: Code for \[Windows Form Call\] Script Task component
Imports System<br>Imports System.Data<br>Imports System.Math<br>Imports Microsoft.SqlServer.Dts.Runtime<br>Imports System.Windows.Forms<br>Imports System.EventArgs<p>Public Class ScriptMain</p><p> Public Sub Main()</p><p> Dim frm As New Form1()</p><p> frm._ServerName = Dts.Variables("Server_Name").Value.ToString()</p><p> frm.ShowDialog()</p><p> Dts.Variables("Server_Name").Value = frm._ServerName<br> Dts.Variables("Action").Value = frm._Action</p><p> frm.Dispose()</p><p> Dts.TaskResult = Dts.Results.Success<br> End Sub</p><p>End Class</p>
Then configure the \[Call Windows Form\] Script Task component. Since we will change package variables, we have to pass it to the Script Task component in a read-write mode. To do so, click the Script item in the left pane and set the ReadWriteVariables property to Server_Name,Action. Finally, save everything and execute the package.
A Versatile Solution
The solution I've described could be helpful for debugging and unit testing in development as well as useful for QA and quality control procedures. I welcome your suggestions on additional uses for the solution as well as feedback on how it worked for you.