SQL2008R2 Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.

While I’m trying to implement transaction handling in SSIS packages i.e  Setting isolation level is ‘Serializable’  and Transaction option into ‘Required’ in the Sequence container properties,

 Error is coming as like below.
[Connection manager "SSIS Package 2008R2"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction."

Solution:
1. Put all the tasks inside the sequence container as per the below screenshot and execute the SSIS package, it will run successfully.

2. Make sure you are executing the package in the server itself not from the local host or other server.
Hope above will helps.!!!!!!!!!

How to execute next step of job from the first step of the same job through condition based query?

Scenario: I came across a situation like job to execute the package based on the conditional check of the database configured in mirror configuration, if it is not configured, second step needs to be run else the job completed with first step output.

Solution: Put this below query in the first step and change the first step of job On Success and On Failure properties as per the below screenshot.

DECLARE @Mirrorstatus int select @Mirrorstatus= COUNT(a.name ) FROM sys.databases A INNER JOIN sys.database_mirroring B ON A.database_id=B.database_id and B.mirroring_state is NULL WHERE a.name='Database_Name' if @Mirrorstatus<=0 BEGIN RAISERROR('50005 A mirroring is currently running.',16, -1, @@servername) --EXEC msdb.DBO.sp_stop_job @job_name='Package_name_' END ELSE BEGIN select @@SERVERNAME END

The first step of the job will check the database exists in mirroring configuration and based on the success result then second job step will execute.Refer the below figure.

SQL2008R2 Error: The remote copy of database "XXXXX" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)

An error occurred while starting mirroring.
Alter failed for Database 'XXXXX'.  (Microsoft.SqlServer.Smo)
The remote copy of database "XXXXX" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)

Solution:
Log backup is not close enough to the mirror server database log. So restore is getting failing in the database mirroring session initiation.

1. Take latest principal database full and log backup with NO RECOVERY mode and copy the backups to the mirror server to restore it by the below order.
2. Restore the full backup with no recovery mode.
3. Then Restore the log backup with no recovery mode.
4. Then start your database mirroring session.

If you are trying to restore the log backup before full backup, you will get the below error.

So the order of backup in the restore is very important. Full backup first and then Log backup to restore.