How To Run SSIS Package

uit · August 11th, 2008, 01:17 PM

Hi All,

I would like to run an SSIS package in VB 2005. It has to be able to be run by a client of mine who requests to "push a button and have the import, load and export" run.

I developed the SSIS package to do the import, load and export as she requested. I suspect that I should create a job that runs the SSIS package but to code it in VB 2005 is where I am lost. VB front-end needs to be able to wait for the package to finish running - which could be 15 minutes or more. Its a very large text import and then 2 exports. So it has to give her some indication that it is still running AND, should it fail, it should give her some error information so she can relay that to me via email or via telephone call. I may look into how to email myself the package error detail, but my main concern how to call an SQL Agent job that calls an SSIS package and keep the user informed throughout the entire process..

Can this be done? Are there any examples anywhere?

Thanks in advance!
Anthony

BrianWren · August 11th, 2008, 02:04 PM

Since you have a VB process that largely does what you want, If it were me, I would use TaskScheduler to run it.

There are a variety of pieces of VB technology that have an "Executing" property. Perhaps you can duplicate that behavior.

You can wrap this in a class, and add a .Completed and .Failed event. The code instantiating the class would then receive an event when the class raises either of those events. Using events removes the task of literall waiting for completion. The VB just goes idle until triggere to do something through the event. (Waiting implies a loop, or using the Sleep API.)

uit · August 11th, 2008, 02:57 PM

Thanks Brian,

"wrap" in a class... a bit advanced for me at this stage though I am wide open to giving that a shot. Any tutorials on how to wrap it in a class, as you say, adding completed and failed events? This sounds like what I need, and sounds like I will have to build it myself - which I am not against. Just never did it... yet.

Anthony

BrianWren · August 12th, 2008, 10:52 AM

Well, there are a lot of tutorials around. Many on the net.

To wrap something in a class you take all of the code and put it in a class. A class starts with

Code:

Public Class TheClassName

and ends with

Code:

End Class

In general, make all of the functions and subs of the original code Private, and add properties or methods to the class which are Public, and which have the sole purpose in life of calling the private components of the original code. If you are calling a function, the public part of the class for that usually would be a property. Properties are the typical way to get a value out of code in a class.

So if you had

Code:

Public Sub Write(Text As String)
    ' Code to write to a resource. Could be dozens of lines.
End Sub

now you would have

Code:

Public Sub Write(ByVal Text As String)
    mWrite Text
End Sub

Private Sub mWrite(ByVal Text As String)
    ' Code to write to a resource.  Could be dozens of lines.
End Sub

Read the Visual Studio Help on creating events. There will be samples & whatnot.

You might want to create a few simple classes and experiment with them to get the general idea.

Defining a class does not create anything. It does not change the size of your compiled code at all.

When you instatiate a class (creating thereby an object that is an instance of that class, then something is created that will take up som eroom in your code, and in memory as the code runs.

Code:

    Dim AnObject As New TheClassName

Think of taking a telephone, and putting it onto a box 4 times the size of the phone, with rods going form the outside of the box to the controls on the phone, 1 for each button. You can't see the phone, nor operate it directly, but you can use all of its functionality through the exposed surface of the box.

So you have all of the phone's functionality, plus you can add features to the box itself. Like a light that flashes brightly in response to the sound of the phone's bell inside the box. Like a display telling when the phone was last used. Like automatically dialing the phone inside it at a certain time and playing a standard message that is a "property" of the box.

You have the phone plus.

Putting running, functional code inside a class gives you similar capability.

Plus, if you want to make your functionality more robust, you can make more than one instance of the class. Each instance runs with no knowledge of the other instances. Any variable that were global to the whol project, if declared Private in the class will be "global" only to the code running in that particular instance. (Things like how many times a routine has been used, the user who is making a request, how long the code has been in existence, and so on, ad infinitim.

uit · August 12th, 2008, 11:09 AM

OK. I can see definite advantages. I am off to code. Thank you!