Call a Windows Form from an SSIS Package

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

    Private Server_Name As String     Private Action As String

    Public Property _ServerName() As String         Get             Return Server_Name         End Get         Set(ByVal Value As String)             Server_Name = Value         End Set     End Property

    Public Property _Action() As String         Get             Return Action         End Get         Set(ByVal Value As String)             Action = Value         End Set     End Property

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)        Handles Button1.Click         Me.Action = "Continue"         Me.Server_Name = Me.TextBox1.Text         Me.Close()     End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)       Handles MyBase.Load         Me.TextBox1.Text = Me.Server_Name     End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)       Handles Button2.Click         Me.Action = "Exit"         Me.Server_Name = "N/A"         Me.Close()     End Sub

End Class

Unfortunately, Visual Basic is your only language choice if you will use the developed code in a SQL 2005 SSIS Script Task component. For SQL Server 2008, 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.

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
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

    Public Sub Main()         '         System.Windows.Forms.MessageBox.Show("Package variables values before            Windows Form call: " & vbCrLf _                  & "Server_Name: " & Dts.Variables("Server_Name").Value.ToString & vbCrLf _                  & "User Action: " & Dts.Variables("Action").Value.ToString & vbCrLf,                     "Package Variables: BEFORE")         Dts.TaskResult = Dts.Results.Success     End Sub

End Class

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
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Windows.Forms
Imports System.EventArgs

Public Class ScriptMain

    Public Sub Main()

        Dim frm As New Form1()

        frm._ServerName = Dts.Variables("Server_Name").Value.ToString()

        frm.ShowDialog()

        Dts.Variables("Server_Name").Value = frm._ServerName         Dts.Variables("Action").Value = frm._Action

        frm.Dispose()

        Dts.TaskResult = Dts.Results.Success     End Sub

End Class

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.