Is ProcessAdd done within a transaction mode?

Is ProcessAdd done within a transaction mode?

This question is very basic. When you do a ProcessAdd, is it within a transaciton mode?

I mean, if the ProcessAdd fault and stop when it meets a mistake, the records before the midstake will still be in the cube or not.

Thanks.

All the operations by default are wrapped into auto-transaction, unless you open an explicit transaction. This includes ProcessAdd too.|||

Thanks,Mosha.

For example, In SSIS, I have a Task1 to ProcessAdd a dimension, which get the data from a source dimension table by a mark column NewData=1 to chose the records. And after the Task1 I have a Task2, inside to update the source dimension table to mark the NewData=0.

I want to know, if the update have some mistakes happend to fault the SSIS package, could I make the Task1's ProcessAdd rollback?

I thinks perhaps that's imposible, because the ProcessAdd have be done already.

Is that right?

|||

You will need to wrap everything into transaction. I am not sure whether standard SSIS processing task can reuse existing transaction, but if not - it will be very easy to write custom transform using AMO, and keep session ID at the pipeline level. Your first task will be to begin transaction, and last task to commit it.

Mosha

|||

Thanks, Mosha.

But I dont think so. You said I need to wrap everything into one transaction, and take the first task to begin transaction, and last task to commit it. But, the transaction your begin is in Sql server, not in Analysis Service. How could the transaction in Sql Server affect the Analysis Server's Process? Or you mean to start a DTC transaction? You can't start a DTC just by use the begin transaction Task and the commit Task method.

By the way, could DTC rollback Analysis Service's Process?

Thanks

|||

> But, the transaction your begin is in Sql server, not in Analysis Service

I meant to start transaction in Analysis Services, not in SQL Server. It can be done with couple of lines of AMO script.

|||

That will meet the same situation. If you start transaction in Analysis Service, not in Sql Server, the transaction will NOT rollback the tasks deal with the Sql Server.

|||I am not sure why you want to do coordinated transaction (and AS doesn't support joining DTC coordinated transaction). If something fails - the processing operation will roll back, even if the dimension table has new records. Isn't it what you needed ?|||

No, I need this:

Task 1 have do ProcessAdd sucessfully.

Task 2 try to change the dimension table's new records mark to old records, which is marked in a NewData column. Task 2 in fact drive a SQL quary like this: update DimensionTable set NewData=0 where NewData=1.

When Task 2 failed, I want to rollback the Task 1. Or it will make a mistake when ETL next time. Because at the next time, Task 1 try to ProcessAdd all the NewData=1's record into the dimenstion.

Thanks.

|||You can do it. At the beginning you start AS transaction. On Success of Task 2 - you do Commit, and On Failure you call Rollback.|||

Thank you, Mosha.

You're right.

I know how to do a begin transaction Task and a Commit Task in SSIS Excute SQL Task. Could you tell me how to do a begin AS transaction Task and a AS commit Task? You mentioned to use AMO to make a self-difined controller. Please tell me more about this or give me some articals about this. I have no idea about that.

Thanks a lot!

|||

I think you need to implement custom script task, and insert a piece of AMO code which starts transaction using Server.BeginTransaction method (see http://msdn2.microsoft.com/en-us/library/microsoft.analysisservices.server.begintransaction.aspx for documentation). Then, when you are ready to do processing, you will need to use AMO again in order to rejoin the same transaction. I didn't check the exact details how to do it - but you can get SessionID property from when you started transaction, and you will need to go to the same session. Finally, in the last task you again use AMO to do CommitTransaction (http://msdn2.microsoft.com/en-us/library/microsoft.analysisservices.server.committransaction.aspx)

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Thanks. But how to join the Standard SSIS AS Task, like the DDL Task into an existed transaction?

Or I can't use the Standard SSIS Task, and need to modify it to a self-controller?

|||

I have an idea:

Firstly, I declare an AS connection in the SSIS. Then I use a Script Task to start an AS Transaction by AMO, and point the transaction to the connection I delcared outside. At the last Task, I do the commit.

Will it be OK?

|||Yes - this sounds OK.