Let me brief the scenario …there are files in the FTP and it was supposed to load data from the flat files to the staging area with absolutely no change or transformation indeed a dump I call it.. .Easy, I said! To myself…..But issue was, if there was any DQ issue in any of the file the package would fail and stop execution and would not load the next file to process…
I said hmmmm…. ! Let me think and gave him a suggestion to set the MaximumErrorCount of the for loop container to any reasonable value (I suggested 9999) and check for it…As expected it swallowed the errors and dint fail the package as the number of errors dint reach the set value…Then just for fun I said lets set MaximumErrorCount to “0” and notice any changes …we ran the package….ooppss!! The package dint threw any error and looped through all the files in the FTP!!! And the for loop container was forced to be evaluated to success!!!
I am still not sure why? Is “Zero” mean “unlimited” may be as I went through many articles but no mention on the same.
He came back with another requirement... and placed another constraint that no data from the file should be loaded if it has even a single record with DQ issue as it was a fast load in OLEDB destination for faster inserts ….
Without Enabling the Transaction for DFT ..
With Enabling the Transaction for DFT ..(set the Transaction Option=Required)
Wearing my thinking caps again! Inserts happen over batches so instead if even transactions for the DFT it will commit the entire data or Rollback if it has any issue but was not sure will it SSIS will handle file transaction the same as other transactions…but the idea worked ..
For more info on the MaximumErrorCount and Transactions here are the links !
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtscontainer.maximumerrorcount.aspx
http://www.mssqltips.com/sqlservertip/1585/how-to-use-transactions-in-sql-server-integration-services-ssis/
Note:make sure Distributed Transaction Coordinator is working for Transaction to happen...Aslo a word of Caution !..Transactions will slow down the process of inserts..Keep a check on that ....
