DTS (VB.NET)

• Download source files - 546 Kb

Introduction

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

2.Select the source server and database

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

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

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

6.Next click on Tansform button of selected Customers table

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

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

9.Then click the Finish button

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

11. Double click the package that created

it will open Design View of package .

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

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.

Qualifier

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)

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

from this window click the Add… button

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

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)

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

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

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

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

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

17 and save the package

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

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

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

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

3.Add the code to execute DTS as shown below

Dim pkg As DTS.Package2
    Try
        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

pkg.LoadFromStorageFile(System.IO.Path.GetFullPath(
    ".\DTSPakages\" & DTSutil.GetDTSFileName), "")

Removing all Global Variable and reassign it

For Each gv As GlobalVariable In pkg.GlobalVariables
    pkg.GlobalVariables.Remove(gv.Name)
Next

pkg.GlobalVariables.AddGlobalVariable("gvSourceServer",
    DTSutil.GetSourceServerName)
pkg.GlobalVariables.AddGlobalVariable("gvSourceServerUID",
    DTSutil.GetSourceServerUID)
pkg.GlobalVariables.AddGlobalVariable("gvSourceServerPWD",
    DTSutil.GetSourceServerPWD)
pkg.GlobalVariables.AddGlobalVariable("gvSourceServerDB",
    DTSutil.GetSourceServerDB)
pkg.GlobalVariables.AddGlobalVariable("gvDestinationServer",
    DTSutil.GetDestinationServerName)
pkg.GlobalVariables.AddGlobalVariable("gvDestinationServerUID",
    DTSutil.GetDestinationServerUID)
pkg.GlobalVariables.AddGlobalVariable("gvDestinationServerPWD",
    DTSutil.GetDestinationServerPWD)
pkg.GlobalVariables.AddGlobalVariable("gvDestinationServerDB",
    DTSutil.GetDestinationServerDB)
pkg.GlobalVariables.AddGlobalVariable("gvTableCustomers",
    DTSutil.GetTableCustomersServerName)

cnt = pkg.Steps.Count
TextBox1.Text = TextBox1.Text & String.Format(
    "PACKAGE EXECUTION BEGINNING") & vbNewLine

Starts the package execution

pkg.Execute()
           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.UnInitialize()
           pkg = Nothing
           cpPoint.Unadvise(intCookie)
           cpPoint = Nothing
           cpContainer = Nothing
           PES = Nothing
       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)
       Finally
 End Try

Thanks

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.