Executing a Package from Visual Basic

So you've created a SQL Server package and now you're ready to integrate it into your Visual Basic application? A common scenario for this is where you don't want a client to have direct access to SQL Server Enterprise Manager. Instead, you can send them a Visual Basic executable to place a clean front-end to the package. For example, you could send the client a program to where you could enter a few parameters to execute the package. In this article, I'll show you how to do a simple example.

The first step is to create a DTS package with one global variable. To create a global variable select Properties from the Package menu then go to the Global Variables tab. For this example, call the global variable gvHello and type the value of "Hi World" (without quotes) as shown below.

Next, create an ActiveX Script Task under the Tasks menu. The only line of code you'll have to add is:

Msgbox DTSGlobalVariables("gvHello").Value

Here's the full task:

Now that you have the package created, save it as SamplePackage locally on the server. Upon execution, you should see the following results from a popup.

Now that your package is created, we're ready to call it from Visual Basic. Open Visual Basic and create a new Standard EXE program. Under the Projects menu, select References. About half-way down, check the Microsoft DTSPackage Object Library item as shown below. This will inform Visual Basic of the DTS object model and enable itellisense to easily find objects for you. It is not required, but it will make your life easier.

Next create a simple Visual Basic app with

• One text box called txtGlobalVariable
• One command button called cmdExecute

It should look something like this:

Double-click on the Execute Package command button to set the command properties. The code is going to be someone simple to perform our basic task. We will want the user to be able to enter a value in the text box and the click on the Execute Package button. When the Execute Package button is set, the global variable will be set to the text box's value and then the package will be executed. To reference the DTS object model, you'll need to declare the object as shown below:

Dim objPackage As New Package

Next, you must load the package. Since we saved the package on the SQL Server, then we will use the LoadFromSQLServer method. If you were to save it as a structured file, then you'd use the LoadFromStructuredFile method. The following lines are an example on how to load the package we saved earlier. My server name is called "ServerName". Notice that I'm using a trusted connection versus a SQL login.

objPackage.LoadFromSQLServer "ServerName", , , _
DTSSQLStgFlag_UseTrustedConnection, , , , "SamplePackage"

To set the global variable, you must use the GlobalVariables collection as shown below:

objPackage.GlobalVariables.Item("gvHello") = txtGlobalVariable.Text

Finally, we execute the package and then unload it as shown here:

objPackage.Execute
objPackage.UnInitialize
 

Here's the complete example: (you can also download it here).

Go ahead, try to execute your program now and enter a sample value:

In the next article, I'll show you a little bit more of the DTS object model and how to add some more sophisticated error handling into your program.