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.

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"