Written by

Developer at Northern Territory Government
Question Arsh Hasan · 2 hr ago

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
}