Doubt in Transaction Scope

Hi ,

I have 2 packages and they get executed in SQL Job.

Each of these package will have seperate transactions with the Database and I am not sure of how to maintain the transactions between these 2 packages.

Can someone explain me the necessary setting to achieve this. If I set the TransactionOption property to Required (or) Supported in both these packages, will the Transaction Scope of the first package extend to the second one.

For me, If the second package ends up in error, the first package should also have to be rollbacked.

Any suggestions would be of great help.

Thanks

Evan

Are the two packages setup so that one is parent and the other is child?

Otherwise, I'm not sure how you can enforce transactions. If so, then set each to "Required" and be sure to set the FailParentOnFailure property to true in the child so that when it fails, so does the parent.

Does the reporting server have to have SQL installed too?

Say the Reporting server has iis and the db server is on a seperate server
running SQL?You can do this. You need a license (SQL Server license) on the box running
RS but you can have the db used by RS on a different server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"User" <User@.User.com> wrote in message
news:eGRKVP2pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Say the Reporting server has iis and the db server is on a seperate server
> running SQL?
>|||Thanks Bruce for your help!!
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uSfU423pGHA.2148@.TK2MSFTNGP03.phx.gbl...
> You can do this. You need a license (SQL Server license) on the box
> running RS but you can have the db used by RS on a different server.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "User" <User@.User.com> wrote in message
> news:eGRKVP2pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> Say the Reporting server has iis and the db server is on a seperate
>> server running SQL?
>>
>