How do I call an SSIS package from IRIS business operation
So I have an SSIS package that I want to call from a business operation.
For that I am using SQL Outbound operation. However, on trying with ExecuteQuery and ExecuteProcedure Functions, I only see an exeuction ID being generated, but the execution never starts.
I tried two methods:
Executing the SSIS package directly from Execute Query function, but again just execution ID was generated. I could not see the execution in the SSISDB logs.
Executing the SSIS package via a Stored procedure and having ExecuteProcedure trigger the procedure, but same problem. I can see the execution ID but no execution is carried.
How do I fix this?
//Stored Proc call code
Method InvokeSSIS(pReq As Ens.Request, Output pResp As Ens.Response) As %Status
{
#dim tSC As %Status = $$$OK
#dim tSnapshots As %ListOfObjects
#dim tOutput As %ListOfDataTypes
#dim snap As EnsLib.SQL.Snapshot
Set pResp = ##class(db.Msg.SSISResponse).%New()
Set tSC = ..Adapter.ExecuteProcedure(
.tSnapshots,
.tOutput,
"{CALL DBName.dbo.Runtheproc}",
""
)
$$$TRACE("Executed stored procedure RunAtoB with status: "_tSC)
If $$$ISOK(tSC) {
$$$TRACE("Successfully executed stored procedure RunAtoB ") //Just confirming if adapter worked and yes it does.
}
If $$$ISERR(tSC) Quit tSC
If $IsObject(tSnapshots), tSnapshots.Count() {
Set snap = tSnapshots.GetAt(1)
While snap.Next() {
Set pResp.ExecutionId = snap.Get("ExecutionId")
Set pResp.StatusCode = snap.Get("ReturnCode")
Set pResp.ErrorText = snap.Get("ErrorMessage")
}
}
Quit $$$OK
}
Discussion (0)0