none
Getting error message and other data back from SSIS to managed code RRS feed

  • Question

  • Hi,

     

    From a SharePoint workflow we are trying to synchronously execute an SSIS package - which seems to work - and display error messages and return some variables back to SharePoint.

     

    I followed the "Running a Package Remotely by Calling a Remote Component or Service" instructions on http://msdn.microsoft.com/en-us/library/ms403355.aspx and executing and parameterizing the package does not seem to be a problem. But the SSIS package does a lot of plausibility checking and raises an error (RAISERROR in T-SQL or Error Outputs in the data flows) and rolls back on an error. I need a human understandable error message for display in SharePoint. I also need to return an identity value to SharePoint if the package succeeds.

     

    The ManagedDTS examples I have seen so far do not show how to return data or error messages from the SSIS package. I could of course write such data to a table and then read the data back in the workflow once the execution is done, but if somehow possible I would like to avoid this.

     

    TIA for any hints. Regards
    Tim

    Wednesday, September 29, 2010 9:42 PM

All replies

  • Hi Tim,

    While using the managed code ManagedDTS to execute a package, the return result is DTSExecResult. The DTSExecResult only has values 0 to 3. 0 means the package is executed successfully.
    In order to return the code, please use the following code:
     DTSExecResult result = package.Execute();
    Or, please convert it to INT32 directly:
     Int result = (Iny32)package.Execute();

    Your idea that write the log to a table and then read the data back should be fine, as the managed code only return the DTSExecResult without the detailed error message. We can configure the SQL Server Integration Services(SSIS) package to log the message into a SQL Server database table, and then read the message when the DTSExecResult is not 0.

    For more information, please see:
    DTSExecResult Enumeration: http://technet.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtsexecresult.aspx
    Logging Package Execution: http://msdn.microsoft.com/en-us/library/ms139845.aspx

    If you have any more questions, please feel free to ask.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Thursday, September 30, 2010 9:22 AM
    Moderator
  • That wasn't all to hard, I must have overlooked the "Warnings" and "Errors" collections of the Package class. I am having high hopes now, that I will also be able to grab a result from the "Variables" collection.

    I cannot currently test this, since ports 135 and 1433 are blocked by the firewall. At least I guess that is the reason for the error messages below. If someone knows better, than I would of course be interested.

    Regards
    Tim

    The SSIS Runtime has failed to enlist the OLE DB connection in a distributed tra
    nsaction with error 0x8004D00E "Die Transaktion wurde bereits implizit oder expl
    izit übertragen oder abgebrochen.".

    SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x8
    004D00E.
    COM error object information is available.  Source: "X2ETLPackage"  error code:
    0x8004D00E  Description: "The SSIS Runtime has failed to enlist the OLE DB conne
    ction in a distributed transaction with error 0x8004D00E "Die Transaktion wurde
    bereits implizit oder explizit übertragen oder abgebrochen.".
    ".

    Failed to acquire connection "10.21.100.120.BIGBI". Connection may not
    be configured correctly or you may not have the right permissions on this connec
    tion.

    Thursday, September 30, 2010 9:23 AM
  • Situation updated and I would still appreciate any help. Running the DTS in managed code works fine, I prepared a tiny console app to run a test package. I can run this executable on the SQL Server machine and will be getting error messages and variable values.

    When I start that same executable on a webserver which can access ports 135 and 1433 of the SQL Server, then I am getting the following error messages from the package:

     

    The task has failed to load. The contact information for this task is "".

    There were errors during task validation.

    The package cannot execute because it contains tasks that failed to load.

     

    I have no clue what went wrong.

    Regards
    Tim

    Thursday, September 30, 2010 1:03 PM
  • COZYROC has recently introduced the commercial SSIS NoW module. You can  now implement SSIS package execution from web service address much easier and without requiring to implement single line of code. Cheers!

    SSIS Tasks Components Scripts Services | http://wwww.cozyroc.com/

    Saturday, November 16, 2019 2:00 PM