• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2295
  • Last Modified:

SSIS 2008 Script Component does not execute PostExecute

In my Data Flow, (within my ForEach Loop container) I am using the Row Count component in a few places to capture any error outputs and need to accumulate the row counts in each place into a package variable.   Then I will handle the appropriate action in the Control Flow, after the ForEach completes.

So I put the Row Count into a variable RowCount1 and right after that in the Data Flow, I have created a Script Component and placed the following in the PostExecute section.  

Variables.ErrorRows1 = Variables.ErrorRows1 + Variables.RowCount1

I also tried Me.Variables... but that did not make a difference.   I placed a MsgBox("test") and that did not appear either.  It looks like PostExecute does not get called at all - and then the Data Flow freezes in yellow, although all of the components inside are all green.

Any suggestions are appreciated!   Thank you.
0
richxyz
Asked:
richxyz
1 Solution
 
radcaesarCommented:
CInt(DTS.Variables("a").value) + CInt(DTS.Variables("b").value)
0
 
richxyzAuthor Commented:
Thanks for the reply, but that made no difference.  The PostExecute is not being called at all.   I have a line MsgBox("test") and I never see the message box appear.
0
 
richxyzAuthor Commented:
I finally got the PostExecute to fire.  I guess I had to be more patient.

When I try your solution, I get "Name DTS is not declared."  

I have the following Imports and don't understand why it can't access the DTS object

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Reza RadCommented:
why you used Script Component to compare variables? you can do it with Script Task in control flow.
and try to use OnVariableValueChanged event instead of postExecute.
But with your explanation I can not guide you exactly.
could you provide brief description of your package? or put screenshots? or put package file here? and then I can help you in details.
0
 
Alpesh PatelAssistant ConsultantCommented:
Yes he (reza_rad:) is right, put your package description.
0
 
richxyzAuthor Commented:
I am attaching the package's control flow and data flow.  Reza, I cannot use a Script Task within a Data Flow (that tool is not available.)  I need to accumulate the row counts of the redirected rows in order to take appropriate action after all of the files are processed.  I thought I would add a Script Component after each Row Count to do this.  As you can see I am using a ForEach loop to process many files.  I will use the Script Task at the end of my control flow to send alerts if there were any redirected rows (errors.)
controlflow.jpg
dataflow1.jpg
0
 
richxyzAuthor Commented:
I forgot to add... if I can accumulate the row counts or all files processed from "Row Count 1" and if it equals the accumulation of "Row Count 99", then all rows were processed successfully.  I need to pass that back to the Control Flow, somehow.  

Thank you in advance for your thoughts on this package.
0
 
Reza RadCommented:
That's OK, you fetched row count into package variable.
But I can't understand why you want to use Event Handlers? what do you want to do with them?
0
 
richxyzAuthor Commented:
I am looping through files with the ForEach Loop and loading them into the database.  If any rows fail, I have them redirected, as you can see to error log files.   If this happens (if there are any errors), I want to send an alert (the last step of the package) notifying me that there were errors that need to be looked at.

Within the data flow, the Row Count stores the last file that was processed.  So if I do not ACCUMULATE the row count into another variable, I will lose what happened in the previous files.  In otherwords, if 3 files fail and the last one is a success, the variables fetched from the Row Count will indicate success only.

If Event Handlers (or Script Component) is not the way to do this, let me know.  I just need to do the following into a Package variable to pass it back to the Control Flow:

AccumulatedErrors1 = AccumulatedErrors1 + RowCount1
0
 
Kalyanum Deepak KumarSenior Technical LeadCommented:
Hi richxyz,

In your initial post , as mentioned, the post execute does not execute, try to use the assign expression in the Input0_ProcessInputRow() function of script component and check. I had experience in assigning varaibles inside the script component and placing all of them in the funciton Input0_ProcessInputRow()  worked, but nothing in postexecute. Notify if the error persists still.
0
 
Reza RadCommented:
seems that you want to find out is there any error rows, then send an alert email for all error rows in a file.
if yes,
you can simply fetch error lines number with a row count transform on error output, and fill result in a variable
and then in control flow right after data flow task, check value of that variable if that is greater than zero, then redirect to a send mail task.

Did I got your problem correctly?
0
 
richxyzAuthor Commented:
@reza_rad, the Data Flow is within a ForEach Loop and is processing multiple files.  Your solution will return the results of the last file processed ONLY.  So that is why I am looking to accumulate the row count results into another variable (within the data flow) which is then returned to the Control Flow after all files are processed.

@deepakkumark, I tried that and got the following error "The collection of variables locked for read and write access is not available outside of PostExecute."
0
 
richxyzAuthor Commented:
@reza_rd, I may have misunderstood what you were saying.   After revisiting my whole package, I see that I can put a script task at the very end (within my ForEach Loop) as you can see from this diagram.   In my Script Task, I am accumulating the rowcounts with the following line:  Dts.Variables("ErrorRows1").Value = CInt(Dts.Variables("ErrorRows1").Value) + CInt(Dts.Variables("RowCount1").Value)

But why doesn't this script task fire?   I tried on Success and on Completion - but the new Script Task at the bottom never turns yellow/gree/red?   Thank you for your help!
notgoingtoscripttask.jpg
0
 
Reza RadCommented:
in the screen shot you put here, set LOGICAL OR at the end of precedence constraint editor window.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now