Calling SSIS packages from within a Script task

Hello,

I was wondering if someone could point me in the direction of any sample code or documentation that allows you to execute SSIS packages from within a Script task?

In SQL Server 2000 DTS this was accomplished by instantiating the DTS.Package object and calling either the LoadFromSQLServer or LoadFromStorageFile method.

I have not been able to find any similiar logic in the SQL Server 2005 SSIS environment.

Any help would be greatly appreciated.

Thank you,

Gordon Radley

Sample code to execute a Package from Script task

Public Sub Main()
Dim pkg As String = "C:\SSISPackage\Package.dtsx"
Dim app As Application = New Application()
Dim p As Package = app.LoadPackage(pkg, Nothing)
p.Execute()
Dts.TaskResult = Dts.Results.Success
End Sub

Hope this helps.

Thanks,
Loonysan

|||

Yes - it definately helps. Thanks for the quick reply.

Do I need to add any additional references if I'm running this from within a Script task in SSIS?

Thanks again!

|||

The Runtime reference is sufficient. You'll want to make sure that you have the Imports statement in your code.

As you've notice, the "Load" methods are now more logically methods of the SSIS Application rather than of the Package itself.

For what it's worth, the documentation team is fleshing out the section on how packages can be loaded, executed, enumerated, and managed programmatically, and how their output can be loaded directly into other applications by using the DtsClient DLL.

-Doug

|||

Doug,

We have discovered issues with DTSClient. Basically you don't get much information out of it. e.g. If a package fails to validate the only message you get is "The package failed to validate" rather than which task failed to validate.

Darren has uncompiled the managed code and noticed some shortcoming in the way it hasbeen implemented. This is wa too low level for me but there are some easy fixes to this. I'm sure Darren would be happy to share his experiences I'm sure.

-Jamie

|||

Darren would never do such a thing, but either way this is not DtsClient. This thread is all about loading and executing a package, directly. For general info (not Jamie) DtsClient does this, but wraps it all up such that you can consume data exposed from the DataReader destination, see the BOL for samples of consuming SSIS from extrenal applications.

When loading and executing you can capture.events, by passing in an implementation of IDTSEvents, see the overloaded methods. If you don't then if anything goes wrong all you really know is it went wrong. By examining the events you get to know why. The package failed validation is a start, but it failed validation because it could not make a connection to SQL Server XYZ in Connection "ABC" is somewhat more helpfull. I strongly suggest you look further at this and implement IDTSEvents.

Jamie got the two confused because DtsClient does not do what I have suggested, and as such is really a nightmare to use real world. Being told something failed is better than not, but if you don't know what failed in a large package you can waste some serious amounts of time trying to find out, we have! For the DtsClient authrors to have not captued the events and exposed the information is just shockingly poor I find, and we no longer use it for that reason.

So to sum up, if you are loading and executing packages then have a look at the overloaded methods that accept IDTSEvents, it will be worth your while when things go wrong. If using DtsClient, I hope it doesn't go wrong for you!

=== Edited by DarrenSQLIS @. 04 Mar 2006 10:17 PM UTC===
Umm... re-reading the thread I now see Doug raised DtsClient, so I was more confused than anyone. Comments still stand, but I'm confused not Jamie.

|||

I have tried as you explained. Nothing happened. I have a top package from here I call another package which contains only script task.

In this script task, the package is called as below.

Dim Lpkg As String = "C:\User Entry Window\LongitudinalLoadingData.dtsx"

Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application

Dim pL As Package = app.LoadPackage(Lpkg, Nothing)

If CStr(Dts.Variables("TypeOfSurvey").Value) = "L" Then

pL.Execute()

else

...Call another package.

End if.

I set the MsgBox message inside the LongitudinalLoadingData, but even this message will not appear. Do I need to add something?