February 27, 2012 at 8:31 am
hi guys,
I am writing to a text log file using Process Task which calls an msdos .bat file.
The .bat takes two arguments: the text to be added to the file and the filepath/filename
This is all working fine.
I want to modify the Process task so that I can pass to the Process Task a variable for the text; the idea is to use the same Process Task to log the start and end of the package rather than have two Process Tasks that are identical except for the Start and End text.
I was thinking that using an environment variable (scoped to the Package) would be the simplest way as I can pass that in as part of the argument expression, but how would I set it. I would prefer NOT to use script as I am a contractor and I don't think they would be able to maintain it after I am gone (hence using a .bat to write to the log file rather than a script).
I was hoping for a Set Variable Task or something similar.
TIA
Obiron
February 27, 2012 at 11:51 pm
How are you going to fire the Execute Process Task at the beginning of the package and at the end of the package as well?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 28, 2012 at 3:11 am
Ermmm,
By adding a Process Task object to the start and end of the process Flow??
Or am I missing something?
[font="Courier New"]ProcessTask:WriteToLog(Start) -> SQLTask:RunScript -> FlatFileTask:DumpTableToCSV -> FileSystemTask:CopyFile -> ProcessTask:WriteToLog(End)[/font]
February 28, 2012 at 3:16 am
aaron.reese (2/27/2012)
... the idea is to use the same Process Task to log the start and end of the package rather than have two Process Tasks that are identical except for the Start and End text.
Wasn't that the whole point of this thread?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 29, 2012 at 12:28 pm
No.., maybe I diodn't explain it clearly enough.
I can write the Process task to build the argument string from variables, What I was struggling to do was to define the variable contents between calls.
ProcessTask:writeFile(myVar)-> do some stuff -> change contents of myVar -> ProcessTask:writeFile(myVar)
Its the underlined bit I don't know how to do. I could use an SQL task and do SELECT 'Some New Text' and set the output to go to [User::myVar] but that seems a bit cumbersome and I am sure there must be a more elegant way.
February 29, 2012 at 1:11 pm
Maybe I'm overlooking something, but if you have 2 Execute Process Tasks, why not have 2 different variables?
Trying to look for an elegant way in SSIS doesn't always work out 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 29, 2012 at 1:35 pm
I want one PT called multiple times. This way if I need to change it's configuration I only have to do it once.
Maybe I am approaching this the wrong way. The ultimate aim is to write to a log file at multiple points in the process flow so I may be calling the PT a dozen times. Is there a better way to append a line of text to a text file without using a PT or a script?
February 29, 2012 at 1:45 pm
aaron.reese (2/29/2012)
I want one PT called multiple times. This way if I need to change it's configuration I only have to do it once.
So I asked how you want to call the Execute Process Task multiple times because you only wanted one EPT. You answered with
By adding a Process Task object to the start and end of the process Flow??
Which means two EPT's. So I suggested to use 2 different variables. To which you respond:
I want one PT called multiple times
That's a bit conflicting. And it brings us back to my original question:
How are you going to fire the Execute Process Task at the beginning of the package and at the end of the package as well?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 29, 2012 at 1:59 pm
OK, I see where the confusion has arisen,
ProcessTask:WriteToLog(Start) and ProcessTask:WriteToLog(End) would be the same object with two process flow arrows into it. The writing of the log would happen concurrently with the next step of the task.
[font="Courier New"]
Process1 -> LogFile
| ^ ^
v | |
Process2 ----- |
| |
v |
Process3---------
[/font]
February 29, 2012 at 2:06 pm
Is this possible in SSIS?
If you have OR constraints on all the paths, the log task will fire once after the first task has finished.
If you have AND constraints on the paths, the log task will fire once after all the other tasks have successfully finished.
Either way, you'll log only once I think.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 29, 2012 at 2:21 pm
If the process task is in an event handler, it can be fired multiple times independently.
I haven't really used the event handlers, because I found that SSIS' built-in logging was sufficient for my needs and MUCH easier to maintain.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply