Click here to Skip to main content
15,867,756 members
Articles / Database Development / SQL Server

Execute DTS Through VB.NET

Rate me:
Please Sign up or sign in to vote.
4.86/5 (12 votes)
22 Dec 2009CPOL10 min read 79.4K   970   46   14
How to execute DTS packages through VB.NET and use global variables.

Table of Contents

Background

Some days back, I was asked to import some text files to our database (MS SQL Server 2000, yes! we are still using it). I used DTS to import these files. Then, I realized, as a programmer or database developer, you can easily import these type of files to a database, but my colleague who is not an expert can not do these on a regular basis. So I thought why not make an application which allows him to just select the files and click on an Upload button to import the files. I searched the net for using DTS in VB.NET, and I found a lot on it, but very few on CP. I developed an application that works fine, and now I am posting my code on CP to help beginners to develop front-end applications using DTS.

(All reference web sites are listed below. Thanks a lot to them.)

Note: As English is my second language, please forgive me for being poor at spelling and grammar.

Introduction

While searching on CP, I found a good article on DTS (VB.NET). It explains using global variables in DTS, neat and clean. I am going to explain DTS in short. (I have copied some content from other sites which are referenced below. For details, please refer to those links.)

  • What is DTS?
  • Data Transformation Services (DTS) in SQL Server 2000 provides a set of graphical tools and programmable objects to help administrators and developers solve data movement problems, including the extraction, transformation, and consolidation of data from disparate sources to single or multiple destinations. Data Transformation Services in Microsoft SQL Server 2000 is a high-performance data pump. It is a tool for copying, moving, consolidating, cleansing, and validating data. The data pump loads each row from the data source, manipulates the values in that row, and inserts the row into the data destination. DTS is also a Rapid Application Development tool for data-oriented programming. It provides a comprehensive set of data manipulation tools organized in a development environment that is both convenient and powerful.

  • What is a DTS package?
  • A DTS package is a set of related objects (connections, tasks, and workflows) that can be used to access, transform, and manipulate data from a wide range of sources including text files and relational databases. DTS was introduced with the release of SQL Server 7.0, and was carried across to SQL Server 2000 (and in SQL Server 2005 as an SISS package) because of its immense popularity.

  • How to create a DTS package?
  • To create DTS packages, we use the DTS designer (which is accessible through the enterprise manager). We will talk more about the DTS designer in a minute, but for now, all we need to know is that there are two types of objects that it can create: connections and tasks. A connection object represents a connection to a data store, such as SQL Server 2000, an Oracle database, or even a text file. They are used to give tasks access to the data they need to transform or manipulate. A task object allows us to work with data accessed through connection objects. Tasks allow us to copy, query, or manipulate data, and are generally responsible for providing any sort of activity in a DTS package. We can store DTS packages on a server as well as a structured stored file on a local drive(s) and then use this package in our application.

(For more details, please go to the referred links.) My agenda of this article is to explain how to execute DTS packages using VB.NET.

Executing DTS packages using VB.NET

There are several ways to execute a DTS package in VB.NET. To use a DTS package through VB.NET, we have to install the following components:

  • We need to install Microsoft SQL Server client tools and Visual Studio 2005 on the computer on which the packages are to be developed.
  • We need to install SQL Server client tools and .NET Framework 2.0 on the computers on which the packages are to be run.

From the COM Component tab of the Add References dialog box, we have to select the references listed in the table below:

Reference

DTS Features

Library File

Microsoft DTSPackage Object Library

Any DTS object or feature

dtspkg.dll

Microsoft DTSDataPump Scripting Object Library

Any transformation supplied with SQL Server or any DTS scripting object

dtspump.dll

Microsoft DTS Custom Tasks Object Library

The Message Queue task, the File Transfer Protocol task, or the Dynamic Properties task

custtask.dll

AddReference.GIF

Add Reference

Retrieving the list of all available DTS packages using VB.NET

There are many ways you can achieve this requirement. You can either use SQL-DMO to do the same, or go for the conventional approach of using some queries on the database. In this example, I am using an in-built Stored Procedure to do the same.

VB
''To Retrieve all the available DTS package
Try
    Dim Cn As New SqlConnection
    Cn.ConnectionString = Get_Connection_String()
    Dim Da As New SqlDataAdapter("exec sp_enum_dtspackages", Cn)
    Dim Dt As New DataTable
    Da.Fill(Dt)
    dgDTS.DataSource = Dt
Catch exSQL As SqlException
    MsgBox("Error: " & exSQL.Message, MsgBoxStyle.Critical)
Catch ex As Exception
    MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical)
End Try

Execute DTS stored on a server

Now, we will see how to execute a DTS package that is stored on a server. It is pretty easy. We have to import the DTS namespace (after adding a reference to our project) and create an instance of the DTS.Package class. Now, we will use the LoadFromSQLServer method of the Package class to execute the DTS on the server.

VB
''Execute DTS Stored on Server
Dim dtsp As New DTS.Package
dtsp.LoadFromSQLServer(ServerName:="ServerName", ServerUserName:="UserName", _
   ServerPassword:="Password", PackageName:="YourDTSPackageName")
dtsp.Execute()
MessageBox.Show("Action completed")
Me.btnUpload.Enabled = False

Executing DTS stored as structured file on a local drive

We can store a DTS package as structured storage file on a local drive for further use in our application. After saving the DTS as a structured storage file, we can simply use it in VB.NET code, like:

VB
Private Sub RunPackage()
    'Run the package stored in file 
    Dim objPackage As DTS.Package2
    Dim objStep As DTS.Step
    Dim objTask As DTS.Task
    Dim objExecobjPkg As DTS.ExecutePackageTask

    objPackage = New DTS.Package
    objPackage.FailOnError = True

    'Create the step and task. Specify the package to be run, 
    'and link the step to the task.
    objStep = objPackage.Steps.New
    objTask = objPackage.Tasks.New("DTSExecutePackageTask")
    objExecobjPkg = objTask.CustomTask

    With objExecobjPkg
        ''.PackagePassword = "user"
        .FileName = My.Application.Info.DirectoryPath & _
                    "\DTSPakages\Import_Stores_DTS.dts"
        .Name = "ExecobjPkgTask"
    End With

    With objStep
        .TaskName = objExecobjPkg.Name
        .Name = "ExecobjPkgStep"
        .ExecuteInMainThread = True
    End With
    objPackage.Steps.Add(objStep)
    objPackage.Tasks.Add(objTask)

    'Run the package and release references.
    objPackage.Execute()

    ''Dim d As Double = objStep.ExecutionTime

    objExecobjPkg = Nothing
    objTask = Nothing
    objStep = Nothing

    objPackage.UnInitialize()
End Sub

Using global variables in DTS

Suppose we want to import an Excel file and we want to pass the Excel file name dynamically to a DTS. We can achieve this by adding a global variable to our DTS package. How to add global variables is explained here: DTS (VB.NET).

I will also try to explain it with the images below:

First, we will import an Excel file to our database using the Import & Export wizard and save this package to SQL Server. Then, we will open the DTS package in the designer window. It will look like this:

1.JPG

Then, we will add a dynamic property task from the task pane and click on the Add button to add a dynamic property to our DTS package.

2.GIF

After this, a dialog box will be displayed. Here, we will select our Excel file connection object, and in the DataSource property, we will double click.

3.JPG

Here, we click on the Create Global Variable button.

4.JPG

A new window will appear. In this window, we click on the New button and add a variable name FileName of type String. Then, we will click on the OK button.

5.JPG

In this window, we will select a variable FileName and than click the OK button. Now we have created a global variable FileName and assigned it to Connection1's dynamic property.

6.JPG

We will now select the connection1 object and the dynamic property task object and go to Workflow and select Success. That's it, our DTS package is ready. Finally, we will save it as a Structured Storage File. To do this, go to the package and click on Save as in the dialog box. Select Structured Storage File in the Save as option and click on Save. We have generated our Structured Storage DTS package. Now, we will execute this DTS through VB.NET code and we will access the FileName Global variable using GlobalVariables's Remove and AddGlobalVariable methods. The code sample is given below:

VB
Private Sub ExecuteDTS(ByVal FILENAME As String)
    Dim objPkg As New DTS.Package2

    Try
        Dim dtsPath As String = My.Application.Info.DirectoryPath & _
                                "\DTSPakages\Import_Stores_DTS.dts"
        'Load Structured Storage File (the DTS)
        objPkg.LoadFromStorageFile(dtsPath, "")
        objPkg.FailOnError = True

        'Remove all Global Variables
        For Each gv As GlobalVariable In objPkg.GlobalVariables
            objPkg.GlobalVariables.Remove(gv.Name)
        Next

        'Set the FileName Global Variable
        objPkg.GlobalVariables.AddGlobalVariable("FileName", FILENAME)

        objPkg.Execute()
        'Release the object
        objPkg = Nothing
        'UnInitialize the object
        objPkg.UnInitialize()

        MsgBox("Data Imported Successfully.", MsgBoxStyle.Information)
    Catch exc As System.Runtime.InteropServices.COMException
        MessageBox.Show(exc.Message, "Error!!!", _
                        MessageBoxButtons.OK, MessageBoxIcon.Error)
    Catch exc As Exception
        MessageBox.Show(exc.Message, "Error!!!", _
                        MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
End Sub

Create DTS in code (without creating DTS on server) at run time

At this point, we are ready to begin coding. The following VB.NET code performs a bulk insert into the Authors table within the Pubs database. The code utilizes the following DTS objects:

  • Package
  • Connection
  • Step
  • Task
  • BulkInsertTask

Package holds everything together, and Step is a rung within the DTS process. These rungs include the individual tasks. Notice in Listing A that the Package and Step objects are declared with a 2 appended to the class name. There are two versions of the classes, with the first version (no number) working with earlier SQL Server versions. You can take advantage of the newer version because you're using SQL Server 2000.

In the code, the Connection object is assigned the necessary properties to connect to the server (e.g., username, password, data source, etc.). Once established, the connection is added to the package. Next, the Step object is created along with the Task objects. Finally, the objects are added to the package, and it is processed via the Execute method.

Basically, the code imports data contained in a text file into the Pubs database. The format for the text file follows:

666-66-6|Tester|Chester|214 243-6666|1 Main Street.|Somewhere|PA|15003|1

The code is enclosed in a Try/Catch/Finally block to handle any unexpected errors. The objects are properly disposed in the Finally block.

The code is simple, and performing such inserts is a common task in most organizations. The code could easily be created in a Windows Service and scheduled to perform the insert on a nightly basis.

VB
'>>Using DTS package withoug creating on server  
Private Sub ExecuteDTS(ByVal FileName As String)
    Dim objPackage As New DTS.Package2()
    Dim objConnection As DTS.Connection
    Dim objStep As DTS.Step2
    Dim objTask As DTS.Task
    Dim objCustomTask As DTS.BulkInsertTask
    Try
        'initialize connection object
    objConnection = objPackage.Connections.New("SQLOLEDB")
    objStep = objPackage.Steps.New
    objTask = objPackage.Tasks.New("DTSBulkInsertTask")
    objCustomTask = objTask.CustomTask
    With objConnection
          objConnection.Catalog = "pubs"
      objConnection.DataSource = "(local)"
          objConnection.ID = 1
      objConnection.UseTrustedConnection = True
          objConnection.UserID = "sa"
      objConnection.Password = "123"
    End With
    objPackage.Connections.Add(objConnection)
    objConnection = Nothing
    With objStep
          .Name = "PkgStep"
      .ExecuteInMainThread = True
    End With
    'initiallize custome task object
    With objCustomTask
          .Name = "PkgTask"
      .DataFile = FileName
          .ConnectionID = 1
      .DestinationTableName = "pubs..stores"
          .FieldTerminator = "|"
      .RowTerminator = "\r\n"
    End With
    objStep.TaskName = objCustomTask.Name
    With objPackage
          .Steps.Add(objStep)
      .Tasks.Add(objTask)
      .FailOnError = True
    End With
    'execute dts package
    objPackage.Execute()
    MsgBox("Data Imported Successfully.", MsgBoxStyle.Information)
    Catch ex As Exception
    MsgBox("Error: " & vbCrLf & ex.Message, vbExclamation, objPackage.Name)
    Finally
    'release objects
    objConnection = Nothing
    objCustomTask = Nothing
    objTask = Nothing
    objStep = Nothing
    If Not (objPackage Is Nothing) Then
          objPackage.UnInitialize()
    End If
    End Try
End Sub

Copy database using DTS

The code given below provides a glimpse at another piece of functionality exposed by DTS objects. It automates the process of copying a database schema and data from one database to another. Please note: the destination database should be created before running the routine. The database uses the same objects as in Listing A, with the exception of TransferObjectsTask2, which is used to perform the actual database copy. The methods of this class are used to set the destination and source databases along with other options.

VB
Private Sub CopyDatabase()
    Dim oPackage As New DTS.Package2()
    Dim oStep As DTS.Step2
    Dim oTask As DTS.Task
    Dim oCustomTask As DTS.TransferObjectsTask2
    Try
        oStep = oPackage.Steps.New
        oTask = oPackage.Tasks.New("DTSTransferObjectsTask")
        oCustomTask = oTask.CustomTask
        oPackage.FailOnError = False
        With oStep
            .Name = "Copy Database design and data"
            .ExecuteInMainThread = True
        End With
        With oTask
            .Name = "PkgTask"
        End With
        With oCustomTask
            .Name = "DTSTransferObjectsTask"
            .SourceServer = "(local)"
            .SourceUseTrustedConnection = True
            .SourceDatabase = "pubs"
            .DestinationServer = "(local)"
            .DestinationUseTrustedConnection = True
            .DestinationDatabase = "pubs2"
            .CopyAllObjects = True
            .IncludeDependencies = False
            .IncludeLogins = False
            .IncludeUsers = False
            .DropDestinationObjectsFirst = True
            .CopySchema = True
            .CopyData = DTS.DTSTransfer_CopyDataOption.DTSTransfer_ReplaceData
        End With
        oStep.TaskName = oCustomTask.Name
        oPackage.Steps.Add(oStep)
        oPackage.Tasks.Add(oTask)
        oPackage.Execute()
    Catch ex As Exception
        MsgBox("Package failed error: " & vbCrLf & ex.Message & _
               vbCrLf, vbExclamation, oPackage.Name)
    Finally
        oCustomTask = Nothing
        oTask = Nothing
        oStep = Nothing
        oPackage.UnInitialize()
    End Try
End Sub

Handling DTS errors in Visual Basic

DTS applications typically consist of two phases:

  • In the first phase, the applications create DTS objects, set their properties, and add them to collections of parent objects. Errors that occur during the object creation/property definition phase can be handled by a typical Visual Basic error handler.
  • In the second phase, the Execute method of the Package2 object is invoked. Errors that occur during Execute will not be propagated back to the caller unless the FailOnError property of the Package2 object is set to True. When FailOnError is True, the description of the returned error will often only tell you that the package failed because a (named) step failed. To determine why a step failed, the GetExecutionErrorInfo method of the Step object will return the properties of a Visual Basic error object that describe the error.

The following code example is a basic error handler that could be used while a package is being developed, and FailOnError is set to True. If failing the package on the first error is undesirable, the sAccumStepErrors function could still be used, but it would need to be called following a normal return from objPackage.Execute, as well as from the error handler.

VB
Private Sub RunPackage()
    'Run the package stored in file
    Dim objPackage As DTS.Package2
    Dim objStep As DTS.Step
    Dim oStep As DTS.Step
    Dim objTask As DTS.Task
    Dim objExecobjPkg As DTS.ExecutePackageTask


    objPackage = New DTS.Package
    objPackage.FailOnError = True

    'Create the step and task. Specify the package 
    'to be run, and link the step to the task.
    objStep = objPackage.Steps.New
    objTask = objPackage.Tasks.New("DTSExecutePackageTask")
    objExecobjPkg = CType(objTask.CustomTask, DTS.ExecutePackageTask)
    objExecobjPkg = objTask.CustomTask

    With objExecobjPkg
        ''.PackagePassword = "user"
        .FileName = My.Application.Info.DirectoryPath & _
                    "\DTSPakages\Import_Stores_DTS.dts"
        .Name = "ExecobjPkgTask"
    End With

    With objStep
        .TaskName = objExecobjPkg.Name
        .Name = "ExecobjPkgStep"
        .ExecuteInMainThread = True
    End With
    objPackage.Steps.Add(objStep)
    objPackage.Tasks.Add(objTask)

    'Run the package and release references.
    Try
        objPackage.Execute()
    Catch ex As Exception
        For Each oStep In objPackage.Steps
            If oStep.ExecutionStatus = _
                     DTS.DTSStepExecStatus.DTSStepExecStat_Completed Then
                If oStep.ExecutionResult = _
                         DTS.DTSStepExecResult.DTSStepExecResult_Failure Then
                    Dim sMessage As String
                    Dim lErrNum As Long
                    Dim sDescr As String
                    Dim sSource As String

                    objStep.GetExecutionErrorInfo(lErrNum, sSource, sDescr)
                    sMessage = sMessage & vbCrLf & _
                            "Step " & oStep.Name & " failed, error: " & _
                            sErrorNumConv(lErrNum) & vbCrLf & sDescr & vbCrLf
                End If
            End If
        Next
    End Try

    ''Dim d As Double = objStep.ExecutionTime

    objExecobjPkg = Nothing
    objTask = Nothing
    objStep = Nothing

    objPackage.UnInitialize()
End Sub

References

About DTS

DTS Programming

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
India India
Still Alive.....

Comments and Discussions

 
QuestionProblems with exec dts saved in filefolder Pin
Andre Paetzold13-Mar-13 1:07
Andre Paetzold13-Mar-13 1:07 
SuggestionExecute DTS Stored on the Server Pin
Rajinder Singh2-Dec-11 6:22
Rajinder Singh2-Dec-11 6:22 
Hi Suresh
Your article is excellent. It helps me lot to run DTS from ASP.NET. Only one suggestion which I found while trying your code, which might be helpful for the newbe's like me, is that we need to create an object of DTS.Step and Insert these line of code after loading DTS from SQL

For Each oStep In dtsp.Steps
oStep.ExecuteInMainThread = True
Next

Thanks
Raj
QuestionNice Article very helpful Pin
_Tushar Patil3-Nov-11 0:24
_Tushar Patil3-Nov-11 0:24 
GeneralMy vote of 1 Pin
Syed J Hashmi17-Mar-10 2:36
Syed J Hashmi17-Mar-10 2:36 
GeneralRetrieving the COM class factory for component with CLSID please help me my code in win server 2003 Pin
Amjad_pk18-Oct-09 20:45
Amjad_pk18-Oct-09 20:45 
GeneralRe: Retrieving the COM class factory for component with CLSID please help me my code in win server 2003 Pin
Santhanamahalingam.S9-May-11 3:30
Santhanamahalingam.S9-May-11 3:30 
Generalmy problem in DTS throgh Vb.net is conversion Access Database to sql server 2005 Pin
Amjad_pk14-Oct-09 16:56
Amjad_pk14-Oct-09 16:56 
GeneralRe: my problem in DTS throgh Vb.net is conversion Access Database to sql server 2005 Pin
Suresh Suthar14-Oct-09 20:51
professionalSuresh Suthar14-Oct-09 20:51 
GeneralRe: my problem in DTS throgh Vb.net is conversion Access Database to sql server 2005 Pin
Amjad_pk15-Oct-09 0:31
Amjad_pk15-Oct-09 0:31 
GeneralGood one ! Pin
Abhijit Jana29-Sep-09 21:57
professionalAbhijit Jana29-Sep-09 21:57 
GeneralRe: Good one ! Pin
Suresh Suthar30-Sep-09 20:46
professionalSuresh Suthar30-Sep-09 20:46 
QuestionHow to create DTS in the Server programmatically? Pin
Viji Raj29-Sep-09 5:33
Viji Raj29-Sep-09 5:33 
AnswerRe: How to create DTS in the Server programmatically? Pin
Suresh Suthar30-Sep-09 2:40
professionalSuresh Suthar30-Sep-09 2:40 
GeneralWaiting for your valuable suggestion and votes!!! Pin
Suresh Suthar14-Sep-09 1:01
professionalSuresh Suthar14-Sep-09 1:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.