Solved

IMPLEMENTING DTS PACKAGE IN VB.NET

Posted on 2006-10-20
25
984 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 12
25 Comments
 
LVL 7

Expert Comment

by:Mohamed Zedan
ID: 17775185
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
ID: 17775212
you could know when the process ended by handling the process event 'Exited'

addhandler p.exited , addressof ProcessExit
0
 

Author Comment

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

0
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!

 
LVL 7

Expert Comment

by:Mohamed Zedan
ID: 17775224
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
ID: 17775230
yes build the console application into an exe and call what i told you  :)
0
 

Author Comment

by:mgmhicks
ID: 17775354
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
ID: 17775506
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
ID: 17775626
are the com components registered on the workstation ?
0
 
LVL 7

Expert Comment

by:Mohamed Zedan
ID: 17775636
print me the references of your project .
0
 

Author Comment

by:mgmhicks
ID: 17775674
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
ID: 17775698
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
ID: 17775754
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
 
LVL 7

Expert Comment

by:Mohamed Zedan
ID: 17775803
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
ID: 17775811
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
ID: 17775836
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
ID: 17775861
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
ID: 17775961
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
ID: 17776015
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
ID: 17776035
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
ID: 17776038
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
ID: 17776073
No I can try that though.  I'll let you know if that works.
0
 
LVL 7

Expert Comment

by:Mohamed Zedan
ID: 17776122
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
ID: 17776139
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
ID: 17776299
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
ID: 17779529
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

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

630 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