Transaction in SSIS

http://sqlage.blogspot.in/

SSIS - Load All Records From Source To Destination OR Load No Record [ SSIS Transactions]

Scenario:

We have to load data from source table to destination table. We want to load all records or no record. That means if any error happen then we want to rollback the transaction.

Solution : 

First, let's study what is the default behavior of our SSIS Package with default settings.

I have created two tables, Create definitions are below and have inserted two rows in Source table.

CREATE TABLE [dbo].[SourceTable](

[Name] [varchar](100) NULL,

[SaleDate] [varchar](50) NULL

) 

insert into dbo.SourceTable 

values ('Aamir','2013-12-03 10:19:56.887'),

('Raza','Test Date')

--Destination Table

CREATE TABLE [dbo].[DestinationTable](

[Name] [varchar](100) NULL,

[SaleDate] [datetime] NULL

)

Notice that I have created SaleDate column in Source table with VARCHAR(50) and in Destination I have that column with data type datetime.

Let's create our SSIS Package and try to load the records in destination table from source without using any transformation. In other words we are not trying to change the data type of source before inserting into destination table.

Bring the data flow task to the control flow pane and then use the ole db source and ole db destination. use the source table in ole db source and destination table in OLE DB destination as shown. Map both the columns.

Let's execute our package and see how many records will be inserted or no record will be inserted?

After execution the package failed and as it was not able to insert 'Test date' value in datetime column in destination table.

Let's check the destination table for the records inserted.

Even the package failed but it inserted the row that was in correct format. But we are interested to load all of them or load no records. 

Let's change the transactionOption to Required instead of Supported and rerun our Package. Before running the package truncate destination table.

To change the TransactionOption to Required, Right Click on Data Flow task and then go to properties and change the TransactionOption to Required as shown below

Let's run our package now and see what we get

The package failed but this time we got below error

"SSIS package "Package5.dtsx" starting.

Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.

Information: 0x4001100A at Data Flow Task: Starting distributed transaction for this container.

Error: 0xC001401A at Data Flow Task: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.

Warning: 0x80019002 at Package5: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package5.dtsx" finished: Failure."

To enable transaction in SSIS Package, we need to have MSDTC service running. Let's enable the service by going to Services.

 Can not find the service with name MSDTC or DTC. The service is with name Distributed Transaction Coordinator. Let's start the service

One more time run the package and see the results

The package failed because of invalid data all the records were roll backed. Non of the record was inserted in destination.

Note :
To enable Transaction the Distributed Transaction Coordinator (MSDTC) should be running