Hello all-
i am having an issue where an SSIS package that calls different powershell scripts that look like they are not completing or not starting but still moves on to the next step in the package. most of the time the package will execute the script and successfully complete and them sometime not. example: SSIS package has a task to send out an email that the process has started and completed but the email does not get sent. or another task where the power shell moves a file from one location to another and renaming the file. on this situation i have seen a file that started as XXXX.csv then gets moved to the other location and is named XXXX.csv.filepart but the .filepart does not get removed.
so i am wondering what can cause, what looks like a disconnect in the process, to just skip part of the process and move on to the next part without completing or not to error.
i am going to build a new package using just SSIS but for future references it would be nice to have an understanding of the possible errors or issues.
thanks
baffled.
July 17, 2020 at 6:18 pm
thank for this reference to look at but that is not what i am wanting. i would like to know what are the possible reasons why powershell works some of the time and other times it would not complete. there is nothing wrong with the code or package. the process works 90% of the time.
July 17, 2020 at 6:55 pm
Do you have appropriate logging and error handling within the PoSh scripts themselves? I have seen cases where scripts have apparently completed 'successfully' (ie, they reported success to the calling application) even though they failed. Adding error handling (TRY/CATCH etc) to ensure that the script properly reports back when it fails should help.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 17, 2020 at 6:56 pm
thank for this reference to look at but that is not what i am wanting. i would like to know what are the possible reasons why powershell works some of the time and other times it would not complete. there is nothing wrong with the code or package. the process works 90% of the time.
Who are you thanking?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 17, 2020 at 7:12 pm
who ever posted this "
You can use BBCodes to format your content.
Available BBCodes include: code, b, i, u, s, center, right, left, justify, hr, sub, sup, color, pre, blockquote, div, ol, ul, li, quote, url, img, youtube, google.
BBCode tags reference
"
July 17, 2020 at 7:16 pm
thanks for the response. i do have logging and for this issue yesterday i have found an error in the logs for a sql script failing which that file did send an email. 3 days ago i saw a file that was partially renamed but there was no error msg in the log for that step.
July 17, 2020 at 7:17 pm
who ever posted this "
You can use BBCodes to format your content.
Available BBCodes include: code, b, i, u, s, center, right, left, justify, hr, sub, sup, color, pre, blockquote, div, ol, ul, li, quote, url, img, youtube, google.
BBCode tags reference
"
That's not a post, it's merely part of the Reply template! 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 17, 2020 at 7:19 pm
hahahahahahahaha. my bad. 🙂
Do you have appropriate logging and error handling within the PoSh scripts themselves? I have seen cases where scripts have apparently completed 'successfully' (ie, they reported success to the calling application) even though they failed. Adding error handling (TRY/CATCH etc) to ensure that the script properly reports back when it fails should help.
I second Phil's comment. If you don't have error handling in the PoSh script itself, the error may not "bubble up" to the initiating process.
Also, as far as I know, there are some issues when using SSIS to execute PoSh scripts and it will most likely run in a disconnected fashion...meaning SSIS will kick the script off and come back saying it's done and it was successful, without waiting for the script to complete. I've personally started moving PoSh script execution to SQL Agent itself, as a separate step in a job...you'll have better luck catching the errors (or at least knowing about them) that way.
July 17, 2020 at 8:08 pm
thanks martin i have seen this case happen many time on other projects with batch files but was unable to confirm this. now that i know someone else has come across this issue, now i dont feel like a crazy person.
🙂
July 17, 2020 at 8:10 pm
thanks martin i have seen this case happen many time on other projects with batch files but was unable to confirm this. now that i know someone else has come across this issue, now i dont feel like a crazy person.
🙂
I can't comment on your sanity (or mine), but this is a "feature" of SSIS 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply