Wednesday, March 2, 2011

Problem Encountered in SSIS Script Component...

While using SSIS Script Component, Data Flow Task was in Yellow color for few minutes and finally provided an exception from the Script Component as

at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariableDispenser100.GetVariables(IDTSVariables100& ppVariables) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.LockReadWriteVariables(String readWriteVariables) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PostExecute()

I found the reason for this issue at the below link
http://www.sqldev.org/sql-server-integration-services/memory-error-in-ssis-transformation-script-component-4422.shtml

According to that it explains the reason for the above exception and the solution as

"The read-write lock is "granted" in (or before) the PreExecute phase of the Data Flow, and only released AFTER the PostExecute phase. All components' PreExecutes are run, THEN the data flow runs, then ALL the components' PostExecutes are run. SSIS does not run the PostExecute of one component in the flow as soon as the last row moves through it. SSIS does not guarantee the execution order of PostExecute calls to the components in the data flow. In effect, it can't - because it can't guarantee that component A will be completely finished before component B starts, and adding any such guarantee would completely muck up any optimization/parallelism it could attempt.

Therefore - you can not have two Scripts - or even two Row Count transforms (or a combination of the two) in a single Data Flow thatwrite to the same variable. Because in each of those cases, SSIS "reserves" the variable from the time the flow starts until it ends.

In order to account for that, you'll need two (or more) variables - one for each "case" you want to report on."

No comments:

Post a Comment