Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Executing a Package from Visual Basic

By Brian Knight, (first published: 2004/01/23)

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).

Private Sub cmdExecute_Click()
Dim objPackage As New Package

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

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

objPackage.Execute
objPackage.UnInitialize

End Sub

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.

Total article views: 25007 | Views in the last 30 days: 10
 
Related Articles
ARTICLE

Using DTS Global Variables

Global variables are a little known solution in Data Transformation Services (DTS). Often packages ...

FORUM

SSIS global variables and package variables

How to create variables to be used in multiple packages.

FORUM

Execute Package Task

Execute Package Task

FORUM

Errors on creating assembly from global dlls

Errors on creating assembly from global dlls

FORUM

SSIS Package Execution Flow Problem

SSIS Package Execution Flow

Tags
dts    
programming    
sql server 7    
visual basic 6    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones