I have created a custom script destination using an article found on TechNet. The script executes fine and completes, but with one small problem. The last package execution log entry states "wrote 0 rows". This is a problem because I use this number to verify that all of the database records have been properly transferred.
I have monitored that database when the script is executing and have verified that the data is being transferred property.
Is there some function that I should be calling with each row prcessed in the custom script destination to "tally" the row count? This way the script component will display the proper row count.
Any suggestions would be appreciated.
You could increment a counter variable in the ProcessInputRow sub and then in the PostExecute sub, assign the value of that variable back to a package-level variable. Then you can, if desired, use an Execute SQL task to insert the contents of the package variable into an audit table. Of course, you could just use the package variable in an expression as well.
I assume you are using this method solely because you are using an ODBC destination? (Just checking)
|||That is a good work around and I think I will implement something like that if I am unable to find a solution.
I still would like to know the "correct" fix on how to output the row count.
Thanks for the post and I will be sure to post any updates if I find anything out.
--Ryan
|||The way to update the row count in that informational message is to call IncrementPipelinePerfCounter on IDTSComponentMetadata90 with the DTS_PIPELINE_CTR_ROWSWRITTEN flag.|||
Ryan Teribery wrote:
I have created a custom script destination using an article found on TechNet. The script executes fine and completes, but with one small problem. The last package execution log entry states "wrote 0 rows". This is a problem because I use this number to verify that all of the database records have been properly transferred.
I have monitored that database when the script is executing and have verified that the data is being transferred property.
Is there some function that I should be calling with each row prcessed in the custom script destination to "tally" the row count? This way the script component will display the proper row count.
Any suggestions would be appreciated.
A custom script destination will not do this by default. The provided destination components have this functionality embedded within them and you would need to embed the same in the script component.
Use:
Me.ComponentMetadata.FireInformation(...)
in your script component to pass out the information that you require. You should use this code in the PostExecute() method.
Counting the number of rows shouldn't be a problem. Just increment a private variable within the ProcessInputRow() method.
Regards
Jamie
|||
That is a good work around and I think I will implement something like that if I am unable to find a solution.
it is not a workaround. This is the correct way to do it.
I still would like to know the "correct" fix on how to output the row count.
The technique that Phil and I have described IS the correct way to do this. Phil's differs slightly because he uses an Execute SQL Task to write a record where mine throws an event which gets caught by your log provider. I think my way is better but then, I'm biased (Phil won't mind me saying that) 
The embedded functionality in the provided transforms (e.g. OLE DB Dest adapter) is the use of the IncrementPipelinePerfCounter method I mentioned earlier. Here's how this works: when that method is called by your destination component, the data flow engine tracks the row count your component passed to it (so you still need to track the row count internall). When the data flow finishes its execution (i.e. after the Cleanup phase), the data flow engine will fire DTS_I_ROWS_WRITTEN with the row count on behalf of the component, resulting in the "Destination Component wrote X rows" message. Your component does not need to post its own message. Also note that the perf counters for rows written will be updated as well, which may be good or bad depending on what you want.
As mentioned by others, there are other ways to handle this. Hope this additional info will be useful in helping you decide how you need to implement this.
