Script component

Question

Script component

sqlgreenhand

SSCommitted

Points: 1639
More actions
February 11, 2011 at 2:33 pm

#246531

There is a PostExecute and PreExecute method under Script component.
What are their purpose and do I have to create them?
Thank you.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andy Leonard SSChampion Points: 10144 More actions · Answer 1

Hi Green,

PreExecute fires when the Script Component is initialized in the Data Flow Task. It's there to allow you do anything you need prior to the script executing. I have used it for initializing variables, but nowadays I initialize variables when I create them.

If it doesn't show up in the code you can create it using the following syntax:

Public Overrides Sub PreExecute()

MyBase.PreExecute()

... your code here

End Sub

I'm pretty sure PostExecute fires after the last row of the last buffer of data flows through the component. If I'm writing variable values - think something like a count of the rows - I do that here.

If PostExecute doesn't show up in the code you can create it using the following syntax:

Public Overrides Sub PostExecute()

MyBase.PostExecute()

... your code here

End Sub

There's a bunch more to the script component than I covered here.

Hope this helps,

Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

sqlgreenhand SSCommitted Points: 1639 More actions · Answer 2

Thank you.

You mention when they got "fired", but are they created by the system or I have to manually produce them?

Some questions, what does the "base"and "Me" mean ? I am using SQL 2005 and will they show up on SQL 2008 too?

Also in "Me.Connections.ThisisConnectionManager", what "imports" I have to attach in order to have "ThisisConnectionManager" appear?

Apologize for having so many issues.

Thanks.

Andy Leonard SSChampion Points: 10144 More actions · Answer 3

Hi Green,

It's ok to have questions. SSIS is a complex topic and you're on one of the steeper slopes of the learning curve.

I think Donald Farmer's book would help you a great deal. It sure helped me:

The Rational Guide to Extending SSIS 2005 with Script

To answer your questions:

...are they created by the system or I have to manually produce them?

The scripting engine changed after SSIS 2005. SSIS 2005 uses Visual Studio for Applications (VSA). SSIS 2008 uses Visual Studio Tools for Applications (or VSTA). I believe VSTA creates overrides for Pre- and PostExecute in the script component always. I cannot remember if the VSA version does it every time. If not, you can add them by adding the sample subroutines above.

...what does the "base"and "Me" mean ?

"Base" refers to a base class from which the current class was inherited. Inheritance is a property of object-oriented programming and allows you to do cool things like build a generic class with a handful of generic properties and methods, then inherit this class in derived classes which may use those same base properties and methods in completely different ways. "Me" is a way to address the current class - the object in which "Me" occurs - from code.

I am using SQL 2005 and will they show up on SQL 2008 too?

As stated earleir, there are differences in the scripting engines used in SSIS 2005 and SSIS 2008. For the most part your code will remain the same. I believe the PreExecute and PostExecute events work the same for the developer; most of the changes are under the hood.

Also in "Me.Connections.ThisisConnectionManager", what "imports" I have to attach in order to have "ThisisConnectionManager" appear?

Accessing the Connections collection from inside a Script component is a complex topic - one that I doubt I can do justice due to my lack of experience using them. I typically use a combination of: 1) source adapters to supply the Data Flow Task any data I require from a Connection Manager; or 2) an Execute SQL Task that pushes a value (or collection of values) into an SSIS variable - and then I consume/shred that variable inside the Script Component or by some other mechanism (like a Foreach Loop Container). Each time I've encountered the need to interact with a Connection Manager inside a Script Component, I've started here (in BOL) and worked my way through the linked articles. That's not a good answer, but it is accurate. There are other SSIS developers who frequent this forum that can likely provide the answers you seek on this topic - and perhaps correct / clarify my answers.

Hope this helps,

Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

sqlgreenhand SSCommitted Points: 1639 More actions · Answer 4

Two things I have to do right now.

First is THANK YOU for the detailed answers for this rookie!

Second is I need to get the book (which already is in your mind) you recommend.

And ..... more questions to come if you do not mind after reading that book ...

😎

Andy Leonard SSChampion Points: 10144 More actions · Answer 5

Hi Green,

You're welcome. SSIS is difficult, but not impossible, to learn. I learned it - that means anyone can! I've also trained hundreds of SSIS developers over the past five years.

I know you can learn SSIS. Just stick with it, especially when it's frustrating.

Hang in there,

Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics