I'm not sure this is the right forum for this but here goes... I have an SSIS package that is loading data to a table in one data flow. A subsequent data flow reads data from that table and loads it (viaa Slowly Changing Dimension task) to a target table. The final target table is in a different SQL database than the source table. The entire package is running in one transaction. The target table has an INSERT/UPDATE trigger that updates a column on the table with the current date (update audit date). When I run this package, the Update path of the SCD fails and I receive several error messages. The errors are: "A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active.". "Uncommittable transaction is detected at the end of the batch. The transaction is rolled back." "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction." The first message always appears twice in the output and the subsequent messages appear once for each row on the update path of the SCD. The trigger is a simple update. It does not return a result set. We have also tried changing the trigger to an insert (to an audit table) to no avail. If I leave the trigger enabled but comment out all of the SQL, the process works. If I disable the trigger, the package works. If I leave the trigger enabled but remove the transaction from the package, it also works. It is only when thetransacation and trigger are both enabled that I have trouble. I have tried this with several input data sets. Any insight would be appreciated. Thanks, Chris

I was able to resolve this issue by setting the "disallow results from triggers"server option using this syntax: sp_configure 'show advanced options', 1 reconfigure sp_configure 'disallow results from triggers', 1 reconfigure I don't fully understand why this works. The BOL article for that setting says: Use the disallow results from triggers option to control whether triggers return result sets. Triggers that return result sets may cause unexpected behavior in applications that are not designed to work with them. When set to 1, the disallow results from triggers option is set to ON. The default setting for this option is 0 (OFF). If this option is set to 1 (ON), any attempt by a trigger to return a result set fails, and the user receives the following error message: "Msg 524, Level 16, State 1, Procedure <Procedure Name>, Line <Line#> "A trigger returned a resultset and the server option 'disallow_results_from_triggers' is true." My trigger doesn't return a result set but the original error seems to indicate that it is trying to which leads me to believe I should have gotten the error above when I executed after changing the setting. What happened instead is that it worked! Go figure. BOL also says: The ability to return result sets from triggers will be removed in a future version of SQL Server. Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do this. To prevent triggers from returning result sets in SQL Server 2005, set the disallow results from triggers Option to 1. The default setting of this option will be 1 in a future version of SQL Server. This statement makes me feel better about changing this server setting but I'd really like to understand the behavior I'm seeing. If anyone has some more input, I'd love to hear it. Thanks, Chris

Actually the trigger is returning a "resultset" of sorts, the number of rows affected. I don't know why this is being interpreted as a resultset but it is.

I had a similar issue today and I realized that this issue can be fixed by putting a SET NOCOUNT OFF towards the end of the trigger. Just having the SET NOCOUNT ON at the top of the trigger is not sufficient. Now you can use sp_configure 'disallow results from triggers' 1, but, that will disable this for the entire database and that may not be desirable, in case if you are expecting some other trigger to return a resultset. Thanks, Maggi