Wednesday, March 7, 2012

How to count the tasks inside a package programmatically?

Hi there,

I'm trying to keep track of the ETL process inserting/updating a row in one table for each package that finish in my ETL process when executing. So far, I created a Script task that increments by one a variable (counter) and then open a connection to my database an insert/update my table. What I want to see is Step 1/30, Step 2/30 and so on. Right know I can display Step 1, Step 2 but how can I get the overall number of tasks within a package?

Thanks in advance for any help you can give me.

Sincerely,
Ricardo

A script task cannot interrogate the package in which it resides to find out that information. Unlike in DTS, tasks do not receive a pointer to package itself. So you can't do what you used to do in DTS i.e. Get a reference to DTSGlobalVariables.Parent and iterate over the object model.

This is one of the big differences in SSIS.

Some extra reading:

But it used to work in DTS (1) - Modifing a Package in script

(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/SSIS_3A00_-But-it-used-to-work-in-DTS-_2800_1_2900_-_2D00_-Modifing-a-Package-in-script.aspx)

-Jamie

|||

You could handle the counting yourself - store one variable with the total count of steps, and another variable that you increment with script tasks as the package executes.

It's a little more work, but it should accomplish what you want.

|||

jwelch wrote:

You could handle the counting yourself - store one variable with the total count of steps, and another variable that you increment with script tasks as the package executes.

It's a little more work, but it should accomplish what you want.

Good idea. Thought you'll probably want to put the script task that increments the value into the OnPostExecute eventhandler scoped to the package.

-Jamie

|||Well, to get the count what I ended up doing is to call the package itself from a Script Task so I can do package.Executables.Count in order to get the total number of tasks inside a package.

I still used the Post-Execute event to increment my counter and to insert/update a row in my audit table.

Thank you guys for the suggestions.

Ricardo|||

bachocha wrote:

Well, to get the count what I ended up doing is to call the package itself from a Script Task so I can do package.Executables.Count in order to get the total number of tasks inside a package.

I still used the Post-Execute event to increment my counter and to insert/update a row in my audit table.

Thank you guys for the suggestions.

Ricardo

So you're building a package solely for the purpose of calling another package? You could of course write a .Net app to do this for you - I find it interesting that you chose not to and instead use SSIS itself as the host for your .Net code.

Anyway...I'm waffling. Glad you got what you wanted.

-Jamie

|||Actually, I think he's calling a new instance of the package from inside the same package. Seems like a lot of overhead, but to each his own.|||Yes, John is right. I'm loading the same package from inside my package using a Script Task. I was thinking to create a new package which only loads the package I want to get the number of tasks and then store this count into a variable.

I don't think I could use a .NET app, I mean I can build it but probably I will not be able to deploy it in production. I can only move dtsx packages. I really would like to implement the best solution for this problem. So if you think that loading the same pacakge from itself is an overhead, what would be the right way?

Thanks!

Ricardo|||

bachocha wrote:

Yes, John is right. I'm loading the same package from inside my package using a Script Task. I was thinking to create a new package which only loads the package I want to get the number of tasks and then store this count into a variable.

I don't think I could use a .NET app, I mean I can build it but probably I will not be able to deploy it in production. I can only move dtsx packages. I really would like to implement the best solution for this problem. So if you think that loading the same pacakge from itself is an overhead, what would be the right way?

Thanks!

Ricardo

Its not an overhead if it doesn't cause any problems. It seems like a strange thing to do but if it works for you - go for it.

-Jamie

|||

bachocha wrote:

So if you think that loading the same pacakge from itself is an overhead, what would be the right way?

"Right way" - I don't know. My comment about overhead was that you are essentially loading the package twice - once to do the real work, once to count the executables. Not a big deal if you are doing it in one or two packages. Across a few dozen, it might have more impact. However, just loading the package doesn't have the same overhead as executing it, so maybe it's not a problem. Like Jamie said, if it works for you, go with it.

No comments:

Post a Comment