1

I have the following Sequence Container inside of a ForEach loop in my SSIS package:

enter image description here

I am busy testing the ROLLBACK TRANSACTION statement, it executes fine but it does not rollback.

Not sure if I am missing anything?

Thanks in advance.

EDIT:

This is how the data flow looks like in my Sequence Container:

enter image description here

Gericke
  • 2,109
  • 9
  • 42
  • 71
  • Can you show the code you have inside of the ForEach loop container's components? Also what is the full code in the Begin Bankmed and Rollback Bankmed? – Zsuzsa Jul 09 '15 at 14:24
  • In your Connection Manager, have you specified `RetainSameConnection=true`? The default is false. – billinkc Jul 09 '15 at 15:30
  • @billinkc yes I have changed that property to true – Gericke Jul 10 '15 at 05:55
  • @G-Man have you read this: https://stackoverflow.com/questions/13626755/how-to-transaction-rollback-in-ssis ? and this article: https://www.mssqltips.com/sqlservertip/1585/how-to-use-transactions-in-sql-server-integration-services-ssis/ – Jacques Bronkhorst Jul 10 '15 at 06:29
  • @JacquesBronkhorst yes I went through that already. I can't use a package transaction, there is a issue with how our SQL admin has been setup. The only solution so far is to execute script tasks – Gericke Jul 10 '15 at 06:32
  • @Zsuzsa I have made an edit so that you can see what is in the Sequence Container – Gericke Jul 10 '15 at 07:53
  • My next question is: did you check if the user who is running the BEGIN TRANSACTION and ROLLBACK TRANSACTION has proper permissions on that database? (I mean the user from the connection string) – Zsuzsa Jul 10 '15 at 08:47
  • @Zsuzsa It is the admin user so it should have – Gericke Jul 10 '15 at 08:49
  • I suggest to start SQL Server Profiler before running the package. Then make sure that all the commands from the SSIS package are run by the same user, within the same connection. – Zsuzsa Jul 10 '15 at 08:56

1 Answers1

0

I took an alternative route to make sure that my fail over works. What I did was I added a column to my tables that will store a GUID and when there is an error I just delete records from tables where that GUID is equal to the one in that is used in that session. I added the GUID in my select statement that my ForEach Container will use and then I write it to the table.

SELECT
    ReconMedicalAidFile.fReconMedicalAidFileID
    ,ReconMedicalAidFile.fReconMedicalAidID
    ,ReconMedicalAids.fMedicalAidID
    ,ReconMedicalAidFile.fFileName
    ,ReconMedicalAidFile.fFileLocation
    ,ReconMedicalAidFile.fFileImportedDate
    ,ReconMedicalAidFile.fNumberRecords
    ,ReconMedicalAidFile.fUser
    ,ReconMedicalAidFile.fIsImported 
    ,CONVERT(varchar(50),NEWID()) AS 'Session'
FROM ReconMedicalAidFile 
INNER JOIN ReconMedicalAids ON ReconMedicalAidFile.fReconMedicalAidID = ReconMedicalAids.fReconMedicalAidID
WHERE (fIsImported = 0) AND (fNumberRecords = 0)

I added this code in the Script Task which will map the selected GUID above:

DELETE FROM BankmedStatments
WHERE fSession = ?

DELETE FROM ReconMedicalAidData
WHERE fSession = ?
Gericke
  • 2,109
  • 9
  • 42
  • 71