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.