I have a SSIS package in which there are lot of Sequence Containers. Each sequence container has two Execute Sql tasks which brings 2 values, and if the comparison of the value is true, it runs a data flow task. Comparison is done using Expression.
If any of the sequence container fails the comparison, I wanted whole package to rollback so I had to use Transactions. I set "Required Transaction" on the package and "Supports Transaction" on each Sequence Container and Tasks respectively.
Now that I am trying to run the package, it runs few sequence containers fine and on few it gives this error.
[Execute SQL Task] Error: Executing the query "SELECT ISNULL(MAX(ClaimBillTypeKey), -1) ClaimBillTypeKey FROM dbo.Dim_ClaimBillType" failed with the following error: "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Also, this is not consistent, one time one Seq. Cont will fail and next time it will run fine but something else will fail. What is going on?
Hello, please anyone? Help?|||I encountered this issue on the RTM version of SSIS. It seem to occur primarily when we were accessing a SQL Server that was behind a firewall. We never did find a solution - we ended up implementing our own transaction and recovery logic.
I haven't tried it in SP1 or SP2, so I'm not sure if might have been fixed by one of those releases.
No comments:
Post a Comment