none
Passing back "error code" from an SSIS package invoked by a stored proc

    Question

  • We use multiple non-.NET languages at our place. We want to call an SSIS 2008 package from a stored proc (as all the non-.NET languages understand how to call stored procs.) Suppose they call a "start job" proc which kicks off the SSIS package. How can we return an error code from the SSIS package execution to the calling program?

    TIA,

    Barkingdog

    P.S I'm thinking of creating a special "workflow" database table that the SSIS package populates as it moves from one step to the next. The result of each step would also  populate the error code field to 0 or not. Of course, then the question becomes, how does the calling program know the SSIS process has completed so it can rely on the values in the database table? 

    P.P.S Our security team will not allow us to use xp_cmdshell and some of the calling languages can only communicate with the database server via DSN. So executing things like "DTSRun" is not allowed.


    Monday, April 09, 2012 4:15 AM

Answers

  • The problem is that calling a stored proc to kick off a job or an SSIS package is asynchronous. You don't get the result of the job of the package, but only if the job/package succesfully started.

    So you need to have some sort of progress table and monitor continously.

    To log the "I'm done record" Russ suggested:

    place an Execute SQL Task at  the end of the control flow. Connect it with the previous task using a precedence constraint se to "completion" (blue arrow). In the execute sql task, use a simple INSERT statement. If you need to log some SSIS variables, such as ExecutionInstanceID, go to the parameter mapping tab and select the system variable from the list.

    Use question marks as placeholders for the parameters:

    INSERT INTO myLog
    SELECT ?

    In the parameter tab, the first parameter gets the name 0, the second 1 and so on. The first question mark in the SQL statement gets parameter 0, the second question mark parameter 1 and so on.

    MCTS, MCITP - Please mark posts as answered where appropriate.

    • Proposed as answer by Eileen Zhao Tuesday, April 10, 2012 7:29 AM
    • Marked as answer by edm2 Wednesday, April 11, 2012 1:41 AM
    Tuesday, April 10, 2012 7:07 AM

All replies

  • The last step of the package could be to write some kind "I'm done" record for the whole package.  I would use the ExecutionInstanceGUID (a system vaiable that produces a unique id for every execution of the package) to tie all of your specific log entries together.


    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

    Monday, April 09, 2012 10:19 AM
  • Russ,

    I'll have to explore this further. You wrote "The last step of the package could be to write some kind "I'm done" record for the whole package."  Can you please provide a few more details as I wasn't aware this could even be done (let alone how to do it.)?

    Barkingdog

    Monday, April 09, 2012 9:04 PM
  • The problem is that calling a stored proc to kick off a job or an SSIS package is asynchronous. You don't get the result of the job of the package, but only if the job/package succesfully started.

    So you need to have some sort of progress table and monitor continously.

    To log the "I'm done record" Russ suggested:

    place an Execute SQL Task at  the end of the control flow. Connect it with the previous task using a precedence constraint se to "completion" (blue arrow). In the execute sql task, use a simple INSERT statement. If you need to log some SSIS variables, such as ExecutionInstanceID, go to the parameter mapping tab and select the system variable from the list.

    Use question marks as placeholders for the parameters:

    INSERT INTO myLog
    SELECT ?

    In the parameter tab, the first parameter gets the name 0, the second 1 and so on. The first question mark in the SQL statement gets parameter 0, the second question mark parameter 1 and so on.

    MCTS, MCITP - Please mark posts as answered where appropriate.

    • Proposed as answer by Eileen Zhao Tuesday, April 10, 2012 7:29 AM
    • Marked as answer by edm2 Wednesday, April 11, 2012 1:41 AM
    Tuesday, April 10, 2012 7:07 AM