Click here to Skip to main content
11,802,762 members (69,988 online)
Click here to Skip to main content


, 23 May 2007 CPOL 110.5K 1.4K 80
Rate this:
Please Sign up or sign in to vote.
Running DTS in VB.NET

Sample Image - DTS__VBNET_.jpg


DTS (Data Transformation Services) is one of the nice and very useful feature that available with SQL Server 2000 . But in case of end user there is not much technical knowledge regarding this so they can't use 'as it is' so they prefer a single click interface for a predefined DTS process ,in SQL Server 2000 not natively supports this(DTS Package saved in SQL Server itself is possible to run with single click but there is certain drawbacks the major one is there is no facility to backing up this DTS package so if system crashes need to re create this package ).There is lot of good article regarding this most of them for DBA but unfortunately there is not even single one found to ready to use (step by step style) and simple in every aspect. Publication of this article is aimed for developers who need simple DTS solution for there .NET applications.

Step By Step

1.Select the Import and Export Data from program menu

Sample screenshot

2.Select the source server and database

Sample screenshot

Here we use SQL Server Authentication method instead of Windows authentication.[ Windows authentication can be used for this if don't want to sets connection property (Server Name,User ID , Password etc ) dynamically ].

3.Select the destination server and database

Sample screenshot

4.Select option copy table(s) and view(s) from the source database option then click next button

Sample screenshot

5.From the next window check the tables that you want to transfer here is it Customers table

Sample screenshot

6.Next click on Tansform button of selected Customers table

Sample screenshot

in Column Mappings tab various option available ,in this example transfer the data in 'Over Write' mode ,then select the Delete rows in destination table option and then clik OK button

7. From the next window deselect the default option Run immediately and then select the save DTS package in SQL server

Sample screenshot

8. Select the Server Instance that you desired to save and give name that appropriate to this package

Sample screenshot

9.Then click the Finish button

Sample screenshot

Sample screenshot

10. After this process select the Enterprise Manager from program menu From that select the SQL Server instance where the DTS package saved . Select the Local packages from the Data Transformation Services Node

Sample screenshot

11. Double click the package that created

Sample screenshot

it will open Design View of package .

12 From design window double on the Delete SQL Task property named Delete from Table….

Sample screenshot

this task is used for delete the rows from destination table .this is the first task of this DTS .In the design window there is green doted arrow(Work flow) indicates that if this task fails no need to execute other tasks in this package that is if it fails the entire task will fail.


Here need some more things to take care .In this Execute SQL Task properties there is a section SQL statement: here we can see the raw SQL query for this task "delete from [Northwind].[dbo].[Customers]" . Import and Export Data utility generates query in this fashion [Server].[owner].[table name] ([Northwind].[dbo].[Customers]) .When this package excute this will maps the database [Northwind] even if we give Catalog Name(Database Name).So we can't change the Database name dynamically .Qualifier should be removed from the query for changing database name dynamically (ie only need to be specify table name ].[Customers] ).But in case of all tasks in a DTS is not possible to remove qualifier at design time ,for instance copy the content to destination table, in this there is no raw SQL query is used to transfer . Another method is used for this type of tasks .How we can achive this ,that will be described in further steps13. Next double click the Transform Data Task Properties (a gray color arrow that connects Connection1 to Connection2)

Sample screenshot

in the source tab from SQL query section remove the qualifier and then click OK button

14. Next from the left Task pan Drag and drop the Dynamic Properties and Task icon to the designer window

Sample screenshot

from this window click the Add… button

Sample screenshot

Here we can find different properties and tasks of current DTS package that can be assigned dynamically .(Connection and its properties like user id ,password ,Server Name Etc) .Click the property that would like set dynamically and click the Set… button

Sample screenshot

Global Variables are used here as dynamic variable to assign properties that sets at runtime .For setting this, Select the Global Variable option from list and click the button Create Global Variables…. (Here is the good point that to create all the Global Variables that are going to be used in this package)

Sample screenshot

When creating Global Variable give the apt name for each variable that easily can understand its purpose (Here in this variable name for Source Server is gvSourceServer)

After setting property click OK button repeat the same steps for each property that want to be set dynamically .

15. As per early step we specify about there is no raw SQL used to transfer data to destination table so it automatically maps the Database and owner name that can't change at design time but in run time. For this we use Dynamic Property Tasks

Sample screenshot

Select the 'Copy Data from …' from the Tasks node of Dynamic Properties Tasks .From that Select the property Destination Object Name and click the Set… button

Sample screenshot

select or create a Global Variable for this here it is gvTableCustomers .

16 . After setting Dynamic Properties next step to set Work Flow

Sample screenshot

for this select this Dynamic properties fist and hold down the control key and select the next task here it is Delete from table … task and select the On Success work flow

Sample screenshot

17 and save the package

18. Next, from Package menu select Save as… and save this DTS Package as structured Storage File .

Sample screenshot

Run this DTS package that stored in structured storage file from a .NET application described in next steps.

DTS Application

1 . To begin, create a new Visual Basic .NET project by opening Visual Studio .NET and selecting New

Sample screenshot

Project. Select Windows Application from the dialog, call the project DTS

2. Then add references Microsoft DTSPackage Object Library from COM tab

Sample screenshot

3.Add the code to execute DTS as shown below

Dim pkg As DTS.Package2
        pkg = New DTS.Package
        'Begin - set up events sink
        Dim cpContainer As UCOMIConnectionPointContainer
        cpContainer = CType(pkg, UCOMIConnectionPointContainer)
        Dim cpPoint As UCOMIConnectionPoint
        Dim PES As PackageEventsSink = New PackageEventsSink(LocalBackgroundWorker,
            TextBox1, txtError)
        Dim guid As Guid = _
            New Guid("10020605-EB1C-11CF-AE6E-00AA004A34D5")
        cpContainer.FindConnectionPoint(guid, cpPoint)
        Dim intCookie As Integer
        cpPoint.Advise(PES, intCookie)
        'End - set up events sink

More about events sink click here

Loading DTS Storage file

    ".\DTSPakages\" & DTSutil.GetDTSFileName), "")

Removing all Global Variable and reassign it

For Each gv As GlobalVariable In pkg.GlobalVariables


cnt = pkg.Steps.Count
TextBox1.Text = TextBox1.Text & String.Format(
Starts the package execution
           TextBox1.Text = TextBox1.Text & String.Format(
              "PACKAGE EXECUTION COMPLETED") & vbNewLine
           TextBox1.Text = TextBox1.Text & String.Format(
               "The package contained {0} steps.", _
              pkg.Steps.Count.ToString) & vbNewLine
           pkg = Nothing
           cpPoint = Nothing
           cpContainer = Nothing
           PES = Nothing
       Catch exc As System.Runtime.InteropServices.COMException
           MessageBox.Show(exc.Message, "Error!!!", MessageBoxButtons.OK,
       Catch exc As Exception
           MessageBox.Show(exc.Message, "Error!!!", MessageBoxButtons.OK,
 End Try


1) NiniNini is an uncommonly powerful .NET configuration library designed to help build highly configurable applications quickly.

2) Background worker ClassAn article on implementing the Whidbey (.NET 2.0) BackgroundWorker component in VB.NET 1.1, and extending it to support multiple arguments.

3) Themed Windows XP style Explorer BarA fully customizable Windows XP style Explorer Bar that supports Windows XP themes and animated expand/collapse with transparency.


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


About the Author

Web Developer
India India
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralMy vote of 1 Pin
Syed Javed17-Mar-10 2:34
memberSyed Javed17-Mar-10 2:34 
GeneralPoor man ;with no knowldge ; Pin
vipinjosea2-Apr-10 14:05
membervipinjosea2-Apr-10 14:05 
GeneralMy vote of 1 Pin
Syed Javed17-Mar-10 2:33
memberSyed Javed17-Mar-10 2:33 
GeneralPoor man , with no knowldge ... Pin
vipinjosea3-Apr-10 3:11
membervipinjosea3-Apr-10 3:11 
AnswerSimple Version Pin
jnedry29-Jan-09 5:17
memberjnedry29-Jan-09 5:17 
Generalthank you! Pin
Z.J.Liu11-Sep-08 7:43
memberZ.J.Liu11-Sep-08 7:43 
GeneralNot user friendly Code.. Pin
suresh suthar9-Sep-08 1:51
membersuresh suthar9-Sep-08 1:51 
Questionplease advice Pin
kagori9-Jul-08 22:27
memberkagori9-Jul-08 22:27 
GeneralProgram on Workstation Pin
ovonic20-Aug-07 7:46
memberovonic20-Aug-07 7:46 
QuestionDTS Pin
sravanthipernati24-Jul-07 22:00
membersravanthipernati24-Jul-07 22:00 
QuestionWondering [modified] Pin
jerry_pendergraft23-Mar-07 7:10
memberjerry_pendergraft23-Mar-07 7:10 
GeneralSource Code Please Pin
Stephen Noronha24-Oct-06 8:21
memberStephen Noronha24-Oct-06 8:21 
GeneralRate this Pin
vipinjosea2-Oct-06 18:22
membervipinjosea2-Oct-06 18:22 
Questionmultiple DTS in one execution Pin
GrangeJM5-Jul-06 5:25
memberGrangeJM5-Jul-06 5:25 
GeneralCurious Pin
gxdata5-Jul-06 1:17
membergxdata5-Jul-06 1:17 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.151002.1 | Last Updated 23 May 2007
Article Copyright 2006 by vipinjosea
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid