Solved

IMPLEMENTING DTS PACKAGE IN VB.NET

Posted on 2006-10-20
25
970 Views
Last Modified: 2008-03-06
I have a application where I need to execute a existing dts package.  Pretty simple.  I have added a module based on code I found to run the dts package through vb.net.  The following is the code in that module.

Imports System.Runtime.InteropServices
Imports DTS

Module Module1

    Sub Main()
        Dim pkg As DTS.Package
        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
            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
            pkg.LoadFromSQLServer("10.10.10.2", "username", "password", _
                DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, _
                "", "", "", "C21ListingsImport", Nothing)
            Console.WriteLine("PACKAGE EXECUTION BEGINNING")
            pkg.Execute()
            Console.WriteLine("PACKAGE EXECUTION COMPLETED")
            Console.WriteLine("The package contained {0} steps.", _
                pkg.Steps.Count.ToString)
            pkg.UnInitialize()
            pkg = Nothing
            cpPoint.Unadvise(intCookie)
            cpPoint = Nothing
            cpContainer = Nothing
            PES = Nothing
        Catch exc As System.Runtime.InteropServices.COMException
            Console.WriteLine(exc.Message)
        Catch exc As Exception
            Console.WriteLine(exc.Message)
        Finally
            Console.ReadLine()
        End Try

    End Sub

End Module
Public Class PackageEventsSink
    Implements DTS.PackageEvents
    Overridable Overloads Sub OnError(ByVal EventSource As String, _
            ByVal ErrorCode As Integer, ByVal Source As String, _
            ByVal Description As String, ByVal HelpFile As String, _
            ByVal HelpContext As Integer, ByVal IDofInterfaceWithError As String, _
            ByRef pbCancel As Boolean) Implements DTS.PackageEvents.OnError
        Console.WriteLine(" OnError in {0}; ErrorCode = {1}, Source = {2}," & _
            " Description = {3}", EventSource, ErrorCode, Source, Description)
    End Sub
    Overridable Overloads Sub OnFinish(ByVal EventSource As String) _
            Implements DTS.PackageEvents.OnFinish
        Console.WriteLine(" OnFinish in {0}", EventSource)
    End Sub
    Overridable Overloads Sub OnProgress(ByVal EventSource As String, _
            ByVal ProgressDescription As String, ByVal PercentComplete As Integer, _
            ByVal ProgressCountLow As Integer, ByVal ProgressCountHigh As Integer) _
            Implements DTS.PackageEvents.OnProgress
        Console.WriteLine(" OnProgress in {0}; ProgressDescription = {1}", _
            EventSource, ProgressDescription)
    End Sub
    Overridable Overloads Sub OnQueryCancel(ByVal EventSource As String, _
            ByRef pbCancel As Boolean) Implements DTS.PackageEvents.OnQueryCancel
        If EventSource.Length > 0 Then
            Console.WriteLine(" OnQueryCancel in {0}; pbCancel = {1}", _
                EventSource, pbCancel.ToString)
        Else
            Console.WriteLine(" OnQueryCancel; pbCancel = {0}", pbCancel.ToString)
        End If
        pbCancel = False
    End Sub
    Overridable Overloads Sub OnStart(ByVal EventSource As String) _
            Implements DTS.PackageEvents.OnStart
        Console.WriteLine(" OnStart in {0}", EventSource)
    End Sub
End Class

I call the module from a vb windows form:

Private Sub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImport.Click

        Module1.Main()

    End Sub

When I run it from developement machine I can get it to go, however I have to remove all the events.  When the events are left in I recieve error in this part of the code

Overridable Overloads Sub OnQueryCancel(ByVal EventSource As String, _
            ByRef pbCancel As Boolean) Implements DTS.PackageEvents.OnQueryCancel
        If EventSource.Length > 0 Then
            Console.WriteLine(" OnQueryCancel in {0}; pbCancel = {1}", _
                EventSource, pbCancel.ToString)
        Else
            Console.WriteLine(" OnQueryCancel; pbCancel = {0}", pbCancel.ToString)
        End If
        pbCancel = False
    End Sub


Object reference not set to an instance of an object. and it points to eventsource.length.

I need to be able to run the dts package and let me know that it was successful or not.  If I run this in a console application it runs fine.   But then how can I use my application to call the console application to run the dts package.  

thanks in advance for the help
0
Comment
Question by:mgmhicks
  • 13
  • 12
25 Comments
 
LVL 7

Expert Comment

by:Mohamed Zedan
Comment Utility
you could call the console application ... like this. :

        Dim p As New System.Diagnostics.Process
        p.Start("path to console application","you can pass arguments here")
0
 
LVL 7

Expert Comment

by:Mohamed Zedan
Comment Utility
you could know when the process ended by handling the process event 'Exited'

addhandler p.exited , addressof ProcessExit
0
 

Author Comment

by:mgmhicks
Comment Utility
Do I build a exe out of console application and call that?

0
 
LVL 7

Expert Comment

by:Mohamed Zedan
Comment Utility
or you could try and check if eventsource is nothing before executing the code in the cancel event handler in your original code
0
 
LVL 7

Expert Comment

by:Mohamed Zedan
Comment Utility
yes build the console application into an exe and call what i told you  :)
0
 

Author Comment

by:mgmhicks
Comment Utility
Ok, 2 things it worked on developer machine, I need to try on a workstation.  Also by looking at the code can you tell me how to get the command prompt boxed closed after execution.  I need it automatically.

Also is there a way to make the console application alert us of errors.  When I remove one of the files in the dts that is to import to sql table, no error or anything occurs in the program.  

Thanks
And you got the points.



0
 

Author Comment

by:mgmhicks
Comment Utility
I installed it on the workstation and I still get the same error I've been getting.  COM

COM object with CLSID {10020200-EB1C-11CF-AE6E-00AA004A34D5} is either not valid or not registered.

Same old stuff.  Had no problem with this in vb but vb.net seems awfully hard to just execute a dts with no variables

Please Help
0
 
LVL 7

Expert Comment

by:Mohamed Zedan
Comment Utility
are the com components registered on the workstation ?
0
 
LVL 7

Expert Comment

by:Mohamed Zedan
Comment Utility
print me the references of your project .
0
 

Author Comment

by:mgmhicks
Comment Utility
Please look at following to see if you think this might be the issue.  This was form another post.

Thanks for your inputs.  I finally found out I need to register the DTS custom task, not dtspkg.dll itself.

I have dtspkg.dll registered on my development platform.  I have added a reference to the COM+ component during development.  Since the applicaiton uses a DTS custom task, the task has to be registered on the web server running the application.  See the link below.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspcust_5p9v.asp

How do I get you a print out of the references?
0
 
LVL 7

Expert Comment

by:Mohamed Zedan
Comment Utility
you could get the references if you expand the references node in the solution explorer window of Visual Studio IDE
0
 

Author Comment

by:mgmhicks
Comment Utility
I thought there was a way to copy and paste them.  Anyway here is what is in the console application as it pretains to DTS

DTS

Here is what is in the application that is calling the console application

DTS
dtsCustTask
interop.dts  (I think this is the issue from what I've been reading)



0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 7

Expert Comment

by:Mohamed Zedan
Comment Utility
exactly the interop.dts has to be registerd in the workstation

you could find out the file name and location of the dll that is interoped into your project by clicking on that reference then hitting F4 for properties you will find the filename and path to the dll in the properties window
0
 

Author Comment

by:mgmhicks
Comment Utility
I get that same error even if I run consoleApplication1.exe from the workstation.  I do not get it from developer system.

thanks again
0
 
LVL 7

Expert Comment

by:Mohamed Zedan
Comment Utility
you should get the error ... you need to register the dll from the developer system copy it to the workstation and register it using regsvr32.exe that should make it work
0
 

Author Comment

by:mgmhicks
Comment Utility
I then receive a erro interop.dts.dll was loaded, but the dllRegisterServer entry point was not found.  This file can notbe registerd.  This is the problem the code in the console application was supposed to take care of.

0
 
LVL 7

Expert Comment

by:Mohamed Zedan
Comment Utility
not the interop.dts.dll
the file that I told you is pointed by in the properties window of Visual studio not the interop

the file you should register should be

programfiles \ microsoft sql server\ 80\tools\binn\dtspkg.dll

register that file and try again
0
 

Author Comment

by:mgmhicks
Comment Utility
The dtspkg.dll exists on the workstation, but the workstation doesnt have microsoft sql so I cant register that path.  If I register dtspkg.dll on the workstation I get loadlibrary("dtspkg.dll") failed - the specifed module could not be found.  I am looking at the file in the folder the appication is installed in.  It has to do with something about creating a wrapper.  Please stick with me on this its really important.

thanks again
0
 
LVL 7

Accepted Solution

by:
Mohamed Zedan earned 500 total points
Comment Utility
did you try installing sql server client utils on the workstation
that basically installs the tools only without the server itself
0
 

Author Comment

by:mgmhicks
Comment Utility
Please see following to see what I am talking about

http://support.microsoft.com/default.aspx?scid=kb;en-us;321525

0
 

Author Comment

by:mgmhicks
Comment Utility
No I can try that though.  I'll let you know if that works.
0
 
LVL 7

Expert Comment

by:Mohamed Zedan
Comment Utility
Are you handeling events from the package... ?
I'll be back in 2 hours ... ok ... as i am going home now see you from there and don't worry i am sticking with you :)
0
 

Author Comment

by:mgmhicks
Comment Utility
Yes, the package is at the top of the post, called in module1.  That is the package I am calling from the application and running.  It is set up exactly like the article I sent you says.

thanks I really appreciate it.
0
 

Author Comment

by:mgmhicks
Comment Utility
Ok installing client tools made it work.  Can we figure out what I have to do in VB code to make it work without installing client tools.

I'll talk to you in a couple of hours.


thanks
0
 
LVL 7

Expert Comment

by:Mohamed Zedan
Comment Utility
Hi ,, After some research I came to the conclusion that in order to execute dts packages from a remote machine you have to install the client tools or :) SQL Server anyother way to do this must include something running on the server
website (ASP page),  Server application that the client or remote application connects to or this method found in this URL using stored procedures

http://www.databasejournal.com/features/mssql/article.php/1459181

but no way without client tools directly ....

the only other way if you don't want to use Stored Procedures or ASP is to make a client server application which communicate through the network to send the execution command to the server and the server reports back to the clients with the progress and status
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now