Accesing package Variable in Conditional Component

Hello Experts,

I am trying to load data to a sql table from flat file..I have a script component which will validate the data. I am also firing the errros when data validation fails. Once I encounter an error I want the whole package to be aborted. What I have done is declared a variable called errorcount (Scope :Package) and I am incrmenting this variable everytime an error is raised and next I have a Conditional Split which will check if this variable count is greater than 0 if yes I will not update the table.This is the only way I think this will work.

error I am getting is :The collection of variables locked for read and write access is not available outside of PostExecute.

I understand they r in the same data flow thats the reason I get this error but how can I by pass it ?

MY dataflow-> Flat File Source-Script Comp-Condtional Split-(If errorCount==0)-SQL Table

How can I redo the dataflow so that this err is not raised.

I have tried to add an output column (blnContinue) and use that in conditional stmt but as this is stored at row level values which have blnContinue as true are getting processed, I dont want this I just want to stop the package execution even if a single validation fails

Pardon my long question

Appreciate your help in advance

You idea will not work for two reasons. 1 the limitation of accessing variables, which is really about performance. 2 if your error is the last row, and you have multiple buffers, the first buffer could have already moved downstream and been written to SQL, as at that point the split would have still been passing.

A better way may be to just throw and exception in the component when you encounter an error. Whilst you still have an issue with data moving downstream before all rows have been checked, but you could workaround this by using a transaction or a commit size of 0 to ensure all or nothing at the SQL end. You could do a similar method by using basic SQL transactions, avoiding DTC, and that way you get to issue the BEGIN TRAN and COMMIT TRAN, or of course when the variable says you have bad data, the ROLLBACK. SSIS Nugget: RetainSameConnection property of the OLE DB Connection Manager - (http://blogs.conchango.com/jamiethomson/archive/2005/08/20/2048.aspx)

Another method would be to stage the data between the validation/cleansing and load. Then check the variable and decide if you want to go on a load SQL afterwards. Raw files are the cheapest phstical persistance, they have very little overhead, just the IO cost really.

|||

Appreciate your response

How do I specify Commit size of 0 to a SQL Destination?

|||For the SQL Server Destination it is called MaxInsertCommitSize, look in the properties grid, I could not see it in the component editor UI.|||

Hmmm...yeah but what if all the files get validated right and there will be some existing records which need to be updates and the otehrs need to be inserted...

If no errors found this is how process should end if found completly abort the process...

Struck ova here....arghhhhh

|||Now you're changing the requirements, but still the options can work. So options now available as I see it are the staging idea, validate everything first before you load into SQL in a second Data Flow task. The other option is to use a transaction. Can be quite expensive, but ultimately that is what they are there for, either a package transaction or the retain same connection method as linked above.