Table of Contents
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.
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.
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
|
Add Reference
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.
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
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.
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
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:
Private Sub RunPackage()
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
objStep = objPackage.Steps.New
objTask = objPackage.Tasks.New("DTSExecutePackageTask")
objExecobjPkg = objTask.CustomTask
With objExecobjPkg
.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)
objPackage.Execute()
objExecobjPkg = Nothing
objTask = Nothing
objStep = Nothing
objPackage.UnInitialize()
End Sub
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:
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.
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.
Here, we click on the Create Global Variable button.
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.
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.
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:
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"
objPkg.LoadFromStorageFile(dtsPath, "")
objPkg.FailOnError = True
For Each gv As GlobalVariable In objPkg.GlobalVariables
objPkg.GlobalVariables.Remove(gv.Name)
Next
objPkg.GlobalVariables.AddGlobalVariable("FileName", FILENAME)
objPkg.Execute()
objPkg = Nothing
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
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:
PackageConnectionStepTaskBulkInsertTask
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.
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
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
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
objPackage.Execute()
MsgBox("Data Imported Successfully.", MsgBoxStyle.Information)
Catch ex As Exception
MsgBox("Error: " & vbCrLf & ex.Message, vbExclamation, objPackage.Name)
Finally
objConnection = Nothing
objCustomTask = Nothing
objTask = Nothing
objStep = Nothing
If Not (objPackage Is Nothing) Then
objPackage.UnInitialize()
End If
End Try
End Sub
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.
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
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.
Private Sub RunPackage()
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
objStep = objPackage.Steps.New
objTask = objPackage.Tasks.New("DTSExecutePackageTask")
objExecobjPkg = CType(objTask.CustomTask, DTS.ExecutePackageTask)
objExecobjPkg = objTask.CustomTask
With objExecobjPkg
.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)
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
objExecobjPkg = Nothing
objTask = Nothing
objStep = Nothing
objPackage.UnInitialize()
End Sub
About DTS
DTS Programming
