I got a solution to make my SSIS work.

Here in my SSIS the process was always getting stuck in the same place wr a table Insert was happening.

So I checked for Constraints in this table and i got to knw there are two foregin key relations to the main two tables which is also part of my transaction. due to this my related table was not able to update/insert

I have removed the foregin key relations to make my SSIS works.

so I will remove the relation before the Dataflow & Once everything commited I will add the relation back.

Note : - Here I am considering the Same SQL side transaction. not the SSIS side.

Hope this will help some of you...

Thanks

Rijosh