The following is a list of questions that I have not been able to obtain concrete answers. I am probably missing something:1) ReadWriteVariables -- can the updated value for a ReadWriteVariable be accessed within the same data flow? It appears not as I think the PostExecute() fires at the completion of the data flow not the end of the Script Component. Secondarily, the Script Component is a non-blocking transformation so the component does not "see" the end of the pipeline prior to sending data down stream. 2) Record Count -- Because of #1 above, How could you calculate a record count for a data stream? It does not appear that one can calculate the number of records for a data stream within a data flow and then access the count from within the same data flow. 3) FinishOutputs() -- Is the concept of FinishOutputs() applicable to Script Component Destinations? Asked another way, is FinishOutputs() executed at the end of the data stream regardless of whether there are "real" outputs for the component? I can create a "Dummy" output to create FinishOutputs() but is this ok?4) Script Component -- It appears that the Script Component Source, Transformation or Destination are really defined based on the columns defined in "Inputs and Outputs". Can you convert an Source script component to a transformation script component by simply adding an Output?Sorry for these basic questions but I am not getting it completely. As you can tell...

1) The PostExecute is where you should write to the variable. I don't know why you get an error when trying to read the value, but you can get around it. This is the code I use: Code Snippet Public Overrides Sub PreExecute() MyBase.PreExecute() Dim vars As IDTSVariables90 = Nothing Me.VariableDispenser.LockForRead("User::MaxNameId") Me.VariableDispenser.GetVariables(vars) CurrentId = CInt(vars("User::MaxNameId").Value) vars.Unlock() End Sub Public Overrides Sub PostExecute() MyBase.PostExecute() Variables.MaxNameId = CurrentId End Sub2) You can't use the variable value of the Row Count transformation in the same data flow. This sounds like you're trying to write a footer to a flat file.3) I don't think FinishOutputs would get called for a destination. That method is for components that will be creating rows. I think it is perfectly okay to create a dummy output. You might also try checking the EndOfRowset property of the Row object. Documentation implies that it will turn true on the last ProcessInput call.4) I don't think you can convert a Source to a Transformation. You would have to add an Input, not an Output, and I doubt the UI will allow that. You should be able to just cut and paste your code, though.

Jay,You are very kind to share your knowledge. You helped big time by answering the two threads I started. I have now successfully created a package which reads the following data and writes to a web service via a .Net package. The .Net package was created by using wsdl.exe to proxy the web service.Item1 Category1Item1 Category5Item1 Category6Item2 Category5Item2 Category8In the above example, I need to delay calling the web service until all rows for an item number have been processed. That was easy, the challenge was getting the last set of items to write. The light bulb came on this morning after reading your reply about 8 times. My mental disconnect was that I felt compelled to use the Script Component Destination (SCD) because I am writing to a destination.Since I am writing the data via an assembly and not a data flow connection, the SCD will not work properly because I am not writing anything the SCD can understand properly. The error trapping will not work.The Script Component Transformation (SCT) works great. The reason being that the response from the web service must be captured and then appended to the data stream so the result can be recorded on the source. Therefore, overwriting FinishOutputs will not work because it does not exist in a SCT. But your point on PostExecute does work. Calling PostExecute allows the last set of items to be properly records.Thank you again for your help!!!

I'm glad you got it working. jessiedev wrote: Therefore, overwriting FinishOutputs will not work because it does not exist in a SCT. But your point on PostExecute does work. Calling PostExecute allows the last set of items to be properly records.FinishOutputs will appear when the script transformation component is asynchronous.

Hi Jay, you wrote this a while back but I am interested if it still applies to having both Pre and Post execute in the same script for a single Script Transformation Editor. Also does it still apply in 2008? Thank you for your help BillBJ Gordon

Hi Jay, you wrote this a while back but I am interested if it still applies to having both Pre and Post execute in the same script for a single Script Transformation Editor. Also does it still apply in 2008? Thank you for your help BillBJ Gordon

To what are you referring? Could you clarify your question? Any type of script component can have neither, one, or both Pre- or PostExecute defined. This applies to all SSIS versions. For more on SSIS variable locking, read this. Talk to me now on

Hi Bill, you should be able to implement every event in the same script component. Pre and post execute events will be fired for once. Hope that helps!

Thanks !BJ Gordon

Hi Todd, I am not new to SSIS but I am very new to the script components. Funny how long I have gone with out having to use them. I am just trying to understand everything that is happening in the script above for the Pre-execute. We are getting the error: Code: 0xC0047018 Source: Solicitors Coverage SSIS.Pipeline Description: component "Track LastCoverageKey" (851) failed the post-execute phase and returned error code 0xC001404D. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:26:33 PM Finished: 1:29:01 PM Elapsed: 148.453 seconds. The package execution failed. The step failed. We were using this method before: ' Microsoft SQL Server Integration Services user script component ' This is your new script component in Microsoft Visual Basic .NET ' ScriptMain is the entrypoint class for script components Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper False )> _ Public Class ScriptMain Inherits UserComponent Private counter As Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) counter = Row.CoverageDLPKey End Public Overrides Sub PostExecute() MyBase .PostExecute() Me .Variables.LastCoverageDLPKey = counter End Class Sub End Sub ' ' Add your code here ' Integer <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _ <CLSCompliant( That works in Dev environment but in our UAT environment from a job it caused the error about. I just want to know why this doesn't work so well. This is what I have implemented today: (notice I have commented out a line because I think it was redundant. I am not sure) Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _ <CLSCompliant(False)> _ Public Class ScriptMain Inherits UserComponent Private counter As Integer Public Overrides Sub PreExecute() MyBase.PreExecute() Dim vars As IDTSVariables100 = Nothing Me.VariableDispenser.LockForRead("User::LastCoverageDLPKey") Me.VariableDispenser.GetVariables(vars) 'counter = CInt(vars("User::LastCoverageDLPKey").Value) vars.Unlock() End Sub Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) ' ' Add your code here ' counter = Row.CoverageDLPKey End Sub Public Overrides Sub PostExecute() MyBase.PostExecute() Me.Variables.LastCoverageDLPKey = counter End Sub End Class Todd please advise. Imports System BJ Gordon

Hi Todd, I added to the thread about script components. I have put my before and after code and feel I have still done something wrong. Please advise if you can. ThanksBJ Gordon

Are you referring to another thread here? What I can see in the code above is that you're mixing the "code method" of reading/writing variables with the "property method". You can't do that. Stick to one or the other, or they'll get in each others' way. The last code snippet you posted above is probably failing due to the code in PreExecute. In there, you're asking for a "code" lock on a variable... but you don't use it in PreExecute. You're attempting to use it in PostExecute via the "property" method. The property method performs it's locking in the PreExecute phase - which directly competes with your code lock attempt in there. (Even though you only use the variable in PostExecute, ALL variables get locked in PreExecute and unlocked in PostExecute by the property method.) Talk to me now on

Hi Todd, I added to the thread about script components. I have put my before and after code and feel I have still done something wrong. Please advise if you can. Thanks BJ Gordon Hi BJ Gordon, Here are two examples of variables in a Script Component: http://microsoft-ssis.blogspot.com/2011/01/how-to-use-variables-in-script.html http://microsoft-ssis.blogspot.com/2010/01/create-row-id.htmlPlease mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com