I created an SSIS packages on VS 2013 using SQL 2014.
I have to use transactions and without MSDTC as we don't have that environment
Ok my issue when I load a stage table during the process and then I select from that same stage table my records on another table are not being inserted. why? I have done sql profiler an all sql statements are being ran under same SPID.
package design
1. For each container - this is to process files on a folder
2. Execute sql task - begin tran
3. sequence container - to hold all and organize containers
4. execute sql task - truncate ImpTableA
5. Data Flow task - Import data into ImpTableA from file
6. execute sql task - insert into DimProducts and new products found in ImpTableA that are not in DimProducts so I'm joining the 2 tables together as a outter join and getting the new ones.
7. Execute sql task - commit tran
8. Execute sql task - rollback tran
on Step 6 I don't find any new rows on DimProducts even though I know there is new products. I have tested the sql statment in SSMS and it works and I find new rows. For some reason SSIS is not finding new products, I'm assuming that the rows are not commited yet and therefore can query them but I thought if I was under the same transaction and SPID I would be able to read uncommited rows.
Any ideas how this can be accomplished?
Need to be able to read from table that was just inserted in a transaction incase of a failure roll everything back.
Other recent topics
