Jason,

Like I say, The solution must fit the problem, and in this case for you and your collegues it does.

Bottom Line, if I want to have all that you mention to run from a web application, using your method I would have to develop some kind of config file that would allow me to send the set switch parameters without having to modify code every time I want to run a different package with different parameters, and this is just not viable. Also, relying on manual intervention after a failure occurs by reading through a typical ssis logfile is a headache and a time waster - I tried this before and it brings back memories of early eighties COM objects and the like - too much information and not enough time to psychologically process it. With my method we only concentrate on areas where the errors occur by passing back the messages/errors instance. I would also have to find some way of executing the set switch from the web application as well. These are just some of the things that I can see that make the method that you describe a little more unmanageable than my method. Below is some example code. I have excluded some information for security reasons but I have supplied an example that shows how the theory would be used if it was implemented. Web Services do so much and for so little because they hide the methods from the calling functions. Your app just calls it and expects a result. It doesn't matter what goes on inside the service, pass/fail is enough for it. Hope this helps to further clarify. Using a service broker this would be hell. ???passing set statements??? This method is easier.

Function MyFunction

Try

ISSIS_FileSys = New MyApplication.SSIS_RunService.SsisWS

Call SetWebReferenceURLs()

'create an instance of an array to carry the variables over to ssis

Dim PackageVars(6) _

As SSIS_RunService.PackageVariable

'this calculates a value that we will pass to one particular variable later

Dim PeriodToRun As String = "00/2008"

PeriodToRun = _

CType(mpContentPlaceHolder.FindControl("ddPeriodMonth"), DropDownList).Text _

& "/" & _

CType(mpContentPlaceHolder.FindControl("ddPeriodYear"), DropDownList).Text

PackageVars(0) = New PackageVariable()

PackageVars(0).Name = "Joblist_ExportPeriod"

PackageVars(0).DataType = PackageVariableDataType.String

PackageVars(0).StringValue = PeriodToRun

PackageVars(1) = New PackageVariable()

PackageVars(1).Name = "RootPath"

PackageVars(1).DataType = PackageVariableDataType.String

PackageVars(1).StringValue = .ProjectPaths.URL.Current

'Pass the name of the user who is creating the files

PackageVars(2) = New PackageVariable()

PackageVars(2).Name = "CreatedByUN"

PackageVars(2).DataType = PackageVariableDataType.String

PackageVars(2).StringValue = GetCurrentUserName()

PackageVars(3) = New PackageVariable()

PackageVars(3).Name = "EASDB_SQLServer_Server"

PackageVars(3).DataType = PackageVariableDataType.String

PackageVars(3).StringValue = ServerName

PackageVars(4) = New PackageVariable()

PackageVars(4).Name = "EASDB_SQLServer_Database"

PackageVars(4).DataType = PackageVariableDataType.String

PackageVars(4).StringValue = DatabaseName

PackageVars(5) = New PackageVariable()

PackageVars(5).Name = "StoredProcConnection_Server"

PackageVars(5).DataType = PackageVariableDataType.String

PackageVars(5).StringValue = StoredProcServerName

PackageVars(6) = New PackageVariable()

PackageVars(6).Name = "StoredProcConnection_Database"

PackageVars(6).DataType = PackageVariableDataType.String

PackageVars(6).StringValue = StoredProcDatabaseName

Dim SSISPackagePath As String = "Packages\ExportFiles.dtsx"

Dim ReturnValue _

As SSIS_RunService.ExecuteSsisPackageInFileSystemResult = _

ISSIS_FileSys.ExecuteSsisPackageInFileSystem( _

SSISPackagePath, _

GrabSSPWD, _

PackageVars, _

"", _

"", _

"")

If ReturnValue.OperationStatus = OperationResult.Success Then

Return ReturnValue

Else

'if failure, then output failure information to

'writelog

HandleException(ReturnValue.Messages, _

"ExportDataFiles", _

Me.Page)

Return ReturnValue

End If

Catch ex As Exception

HandleException(ex, "ExportDataFiles", Me.Page)

Return Nothing

End Try

End Function

Notes:

SSIS_RunService is the name of the WCF service running on a sql server.

Set_WebReferenceURLs is a function that is a couple of lines that dynamically sets the http location of the ssis_RunService service, so that I can port between dev/qua and prod quite nicely.

HandleException is a function that I have to write exception logs to a logfile in the event that things go pear shaped in production.