I have a situation where I run the same taks multiple times during the execution. I would like to have one task which runs every time, instead of duplicating the task over and over 14 times in my script.
Basically, it is an audit log, which I set variables and then insert into a SQL table the variables.
I would like to do this:
Task1 Success--> Set Vars --Success--> Log
|
Task2 Success--> Set Vars --Success-| (do the Log task again)
|
Task3 -Success--> Set Vars --Success-| (do the Log task again)
|
etc
This works, however, I have to duplicate Log over and over and over. No OR does not work, because it still only executes the Log task once.
Another option I thought of, but cannot find a way to implement is: Make the Log task "disabled" with no dependencies, then in the Set Vars script, enable and execute Log and disable again.
Any ideas?I take it these are variables in SetVars are a custom variable set, since the default SSIS logging can handle many logging situations.
This looks like a case for event handlers.
Create three package scoped event handlers
OnPreExecute:
Set a package scoped variable IsFailed to false
OnTaskFailed:
Set a package scoped variable IsFailed to true provided the task is
one of the (Task1,Task2,Task3 or SetVars tasks)
OnPostExecute:
Check the package scope IsFailed variable
and log as needed (only one log implementation and instance needed)
There are scenarios were this would not work, involving parallelism of tasks, but the depiction you have would appear to be task1 followed by task2...|||It seems that you may want to look into SSIS logging capabilities and custom logging component http://www.thejoyofcode.com/SSIS_Custom_Logging_Task_for_Event_Handlers.aspx. There is also Microsoft example for it I reckon (html and smtp log provider samples)- http://msdn2.microsoft.com/en-us/library/ms365179.aspx
In case you have requirement doing it in task - I would recommend creating a custom task - http://msdn2.microsoft.com/en-us/library/ms345156.aspx. In this case of custom "Log" task - the code would not be duplicated - although you would still need to drag and drop it 14 times to designer and link it|||Ok. LOG was a bad wording. I was trying to get is how to run the same task multiple times.
Lets just say for argument the "LOG" task above is a "Send Email Task", it doesn't matter what the task does. I don't want 14 send email tasks which are exactly the same. I want ONE task and have it execute 14 times.
The best thing, would be if there was a "shortcut" mechanisim, which doesn't exist, to allow this. So I could "reference" the orginal task, even if I had to have 13 shortcuts to the original task that would be ok with me.|||
 Tom Phillips wrote: |
|
| Ok. LOG was a bad wording. I was trying to get is how to run the same task multiple times. Lets just say for argument the "LOG" task above is a "Send Email Task", it doesn't matter what the task does. I don't want 14 send email tasks which are exactly the same. I want ONE task and have it execute 14 times. The best thing, would be if there was a "shortcut" mechanisim, which doesn't exist, to allow this. So I could "reference" the orginal task, even if I had to have 13 shortcuts to the original task that would be ok with me. |
The smallest unit of reusability in SSIS (currently) is a package. hence if you want to reuse some functionality anywhere you want you should split it out into a seperate package and call it. This works quite well.
its possible that eventhandlers (as mentioned previously) could solve the problem also although without knowing more about your particular situation I can't make a call on that.
-Jamie
|||
| Jamie Thomson wrote: |
|
The smallest unit of reusability in SSIS (currently) is a package. hence if you want to reuse some functionality anywhere you want you should split it out into a seperate package and call it. This works quite well. its possible that eventhandlers (as mentioned previously) could solve the problem also although without knowing more about your particular situation I can't make a call on that. -Jamie |
Jamie,
"The smallest unit of reusability in SSIS (currently) is a package" is the key.
Under any circimstance, I have to duplicate something, either the execute package or send email or in the above event handler example 3 TIMES 14 tasks, over and over and over.
I would like to suggest to MS they add a "shortcut task" which "references" another task. So only one ocurance of the actual task exists. Even though I would have to duplicate the "shortcut" over and over, if I make a change to the original task it would propagate to all the "shortcuts". There are some techincal details to work out on this, for example, what about expressions in the original, do they run or not, can you override them in the "shortcut"? But I think it would be doable.
In my case I have 14 tasks which are identical, and I had to change one thing in every single one of them. What happens when there are 30, or 100 of them? The shortcut would have eliminated that problem.
Thank you|||
| Tom Phillips wrote: |
|
| Jamie Thomson wrote: | | The smallest unit of reusability in SSIS (currently) is a package. hence if you want to reuse some functionality anywhere you want you should split it out into a seperate package and call it. This works quite well. its possible that eventhandlers (as mentioned previously) could solve the problem also although without knowing more about your particular situation I can't make a call on that. -Jamie |
|
Jamie,
"The smallest unit of reusability in SSIS (currently) is a package" is the key.
Under any circimstance, I have to duplicate something, either the execute package or send email or in the above event handler example 3 TIMES 14 tasks, over and over and over.
I would like to suggest to MS they add a "shortcut task" which "references" another task. So only one ocurance of the actual task exists. Even though I would have to duplicate the "shortcut" over and over, if I make a change to the original task it would propagate to all the "shortcuts". There are some techincal details to work out on this, for example, what about expressions in the original, do they run or not, can you override them in the "shortcut"? But I think it would be doable.
In my case I have 14 tasks which are identical, and I had to change one thing in every single one of them. What happens when there are 30, or 100 of them? The shortcut would have eliminated that problem.
Thank you
Tom,
What you're talking about is reusability of tasks. This is my number one request (in a list of literally hundreds) for the next version of SSIS. Unfortunately I don't think we'll see it in SQL Server 2007/8 which is intended to be a small release after the masses in SQL Server 2005, but hopefully in the one after that.
Until then, lump all your reusable stuff into a dedicated package.
-Jamie
|||I would hope to see something before 2022.
I "think" the shortcut method would be realtively easy to do. I wonder if I could create a custom task which allowed referencing another task, basically copy the functionallity at runtime? I will have to look into it.
Thanks
