December 19, 2006 at 12:57 pm
I have a Data Transform Task that copies the data from a CSV file to an SQL 2000 database table. If there is an error in copying, I am sending an email. Upon successful copying of data, I am copying (using activex scripting task) the original source .CSV file to the destination folder.
If the copy of files to new destination folder fails, due to what ever reason, I would like to roll back the transactions (created by Data Transform Task) . How can I do that in DTS?
thanks
nath
December 19, 2006 at 2:52 pm
According to "Professional SQL Server 2000 DTS", an Active-X Script task can't join the package transaction, so you wouldn't be able to rollback the transaction if the file copy failed. Maybe you could use one of the other methods mentioned in your previous thread for copying the file.
Greg
Greg
December 20, 2006 at 6:32 am
I have wrapped items in a transaction using execute sql statements. This post gave me reason to fix some problems.
First, create an executesql task that has a "begin transaction" statement on the SQL connection. If you are clearing reconds from the SQL table, put that statement after the begin transaction. This has to be joined in workflow to the data load.
Remove the fast load on the task to load the sql table from excel (must do this to get the load to join the transaction). This is on that final tab.
Create two ExecuteSQL tasks on the SQL connection. One is simply Commit transaction the other Rollback transaction.
Put your activex script after the load task (in workflow). If you succeed in copying, return the succes constant (that is the "main = DTSTaskExecResult_Success" line that is added by default when building an ActiveX task. If you can't copy the file then return the failure constant ("main = DTSTaskExecResult_Failure").
Make sure that the load is connected to the activex script. Next, add an "on success" workflow from the activex to the "commit trans" executesql task. Add and "on failure" workflow from the activex to the "rollback trans" task.
Let me know if this works for you.
Russel Loski, MCSE Business Intelligence, Data Platform
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply