Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 991
  • Last Modified:

IMPLEMENTING DTS PACKAGE IN VB.NET

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
mgmhicks
Asked:
mgmhicks
  • 13
  • 12
1 Solution
 
Mohamed ZedanCommented:
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
 
Mohamed ZedanCommented:
you could know when the process ended by handling the process event 'Exited'

addhandler p.exited , addressof ProcessExit
0
 
mgmhicksAuthor Commented:
Do I build a exe out of console application and call that?

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Mohamed ZedanCommented:
or you could try and check if eventsource is nothing before executing the code in the cancel event handler in your original code
0
 
Mohamed ZedanCommented:
yes build the console application into an exe and call what i told you  :)
0
 
mgmhicksAuthor Commented:
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
 
mgmhicksAuthor Commented:
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
 
Mohamed ZedanCommented:
are the com components registered on the workstation ?
0
 
Mohamed ZedanCommented:
print me the references of your project .
0
 
mgmhicksAuthor Commented:
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
 
Mohamed ZedanCommented:
you could get the references if you expand the references node in the solution explorer window of Visual Studio IDE
0
 
mgmhicksAuthor Commented:
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
 
Mohamed ZedanCommented:
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
 
mgmhicksAuthor Commented:
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
 
Mohamed ZedanCommented:
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
 
mgmhicksAuthor Commented:
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
 
Mohamed ZedanCommented:
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
 
mgmhicksAuthor Commented:
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
 
Mohamed ZedanCommented:
did you try installing sql server client utils on the workstation
that basically installs the tools only without the server itself
0
 
mgmhicksAuthor Commented:
Please see following to see what I am talking about

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

0
 
mgmhicksAuthor Commented:
No I can try that though.  I'll let you know if that works.
0
 
Mohamed ZedanCommented:
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
 
mgmhicksAuthor Commented:
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
 
mgmhicksAuthor Commented:
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
 
Mohamed ZedanCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 13
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now