July 14, 2021 at 2:32 pm
Hmmm...your PS script might not even be initiated at all.
What is your local PS execution policy, and is the account you're using a local administrator?
July 14, 2021 at 2:56 pm
Hmmm...your PS script might not even be initiated at all.
What is your local PS execution policy, and is the account you're using a local administrator?
Yes, I am local admin. Since I'm running SSIS in BIDS, it should be using my admin permissions for everything. I don't see that it could be breaking anything.
Default execution policy is RemoteSigned but the process task is using "-windowstyle Hidden -ExecutionPolicy Bypass -ExecutionPolicy Unrestricted -Command '\\NAS\Share\With\Powershell\Scripts\My PowerShellScript Get Header Information.ps1'". Since other powershell scripts are working in the jobs using the same arguments, I don't see how a GPO could be preventing the use of Bypass and Unrestricted. Otherwise the other jobs would fail.
And yes, I'm aware of the safety concerns using those execution policies. I need to reach out to our server admin regarding the remote signing thing because I cannot (as in not allowed to by company policy) create a signing certificate myself.
July 14, 2021 at 3:01 pm
The only other thing I can try to think of here is to run Visual Studio as admin. Even with your local admin permissions, the VS process may not have the necessary permissions to execute the other process.
July 14, 2021 at 5:51 pm
Running VS as Administrator didn't work. I tried copying the package to my desktop and running it in 2019 and that didn't work.
I'm going to ask a coworker who has 2012 to try and then also try remote signing the powershell script after consulting with my server admin peeps. Something has to work, one would think.
July 14, 2021 at 6:01 pm
Yeah...I would try unrestricted for the local machine (not user) too, if that's an option.
July 14, 2021 at 6:07 pm
Yeah...I would try unrestricted for the local machine (not user) too, if that's an option.
I dearly wish I could, just to test, but Corporate had a whole to-do about removing those execution policies from the machines and they are monitoring daily. I'd be fileted if I even tried.
July 14, 2021 at 7:16 pm
I wonder if using ExecutionPolicy Unrestricted in the command line is overriding the previous Bypass setting on the command line? You don't actually need to specify both - one or the other. Bypass is supposed to ignore all file checking where Unrestricted does check some of them. It the execution policy is actually unrestricted - then a file on a NAS could be one of those cases where it would check.
I still think you would be much better off using a script task - where you can embed the code or import the code - or just add the script and invoke that script. If you decide to add a script and invoke - here is how you could implement that in your script task:
using Microsoft.PowerShell;
using System.Management.Automation;
using System.Management.Automation.Runspaces;
...
public class MyClass
{
public void MyMethod()
{
// Create a default initial session state and set the execution policy.
InitialSessionState initialSessionState = InitialSessionState.CreateDefault();
initialSessionState.ExecutionPolicy = ExecutionPolicy.Bypass;
// Create a runspace and open it. This example uses C#8 simplified using statements
using Runspace runspace = RunspaceFactory.CreateRunspace(initialSessionState);
runspace.Open();
// Create a PowerShell object
using PowerShell powerShell = PowerShell.Create(runspace);
// Add commands, parameters, etc., etc.
powerShell.AddCommand(<command>).AddParameter(<parameter>);
// Invoke the PowerShell object.
powerShell.Invoke()
}
}
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 14, 2021 at 7:53 pm
I still think you would be much better off using a script task - where you can embed the code or import the code - or just add the script and invoke that script.
I agree, Jeffrey. Managed code will work better than the Execute Process task, which is pretty much a black box.
July 14, 2021 at 8:49 pm
I wonder if using ExecutionPolicy Unrestricted in the command line is overriding the previous Bypass setting on the command line? You don't actually need to specify both - one or the other. Bypass is supposed to ignore all file checking where Unrestricted does check some of them. It the execution policy is actually unrestricted - then a file on a NAS could be one of those cases where it would check.
I've tried running this with just Bypass and then just Unrestricted. Neither made a difference.
I still think you would be much better off using a script task - where you can embed the code or import the code - or just add the script and invoke that script.
My scripting abilities are woefully subpar. It's why I tend to use existing tasks to do what I can rather than throwing script tasks in my packages. However, when in need... And you kindly included code so I don't have to spend a century looking up what I need and trying to figure it out from the samples. THANK YOU!
EDIT: You know if I'd bothered to read the code before I posted my response, I would have had the answer to my question. DUH. Thanks, again.
July 14, 2021 at 9:00 pm
My scripting abilities are woefully subpar. It's why I tend to use existing tasks to do what I can rather than throwing script tasks in my packages. However, when in need... And you kindly included code so I don't have to spend a century looking up what I need and trying to figure it out from the samples. THANK YOU!
Quick question, is this VB or C#?
It is C# and the example is not from SSIS - so there will need to be changes to incorporate into a script task. You need to add the import statements to the template that SSIS provides, and incorporate the code from the method into the main section of the script task.
It seems there is something blocking the execution of the script - have you tried copying the script to a local drive and just executing the script directly from Powershell (or Powershell ISE)? If it works when the file is local - try executing it directly from the NAS and see if you get any errors or messages.
Without being able to see the script itself - there really isn't a way for us to determine if the script itself is the problem. It is possible the script is hiding any errors and returning success regardless of execution. If the script has set the error level to silently continue - it would do just that and basically ignores errors.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 14, 2021 at 9:08 pm
It seems there is something blocking the execution of the script - have you tried copying the script to a local drive and just executing the script directly from Powershell (or Powershell ISE)? If it works when the file is local - try executing it directly from the NAS and see if you get any errors or messages.
I know I've said this before in this thread. I can run the script from Powershell without any issues. The script isn't local to the PC. It's on a NAS share. Since I can run it off the NAS, I'm not going to copy it local. I doubt the script's location is the issue.
Without being able to see the script itself - there really isn't a way for us to determine if the script itself is the problem. It is possible the script is hiding any errors and returning success regardless of execution. If the script has set the error level to silently continue - it would do just that and basically ignores errors.
The reason I haven't posted the script is because I can execute it manually. So I know the script itself isn't the problem. It's something in the SSIS package or the attempt to call the script from the SSIS package. I know the SSIS package can see the NAS because it does other tasks related to the NAS without issue (file copies, for instance).
But if anyone feels the need to see the script, just let me know and I'll scrub-n-post it.
July 15, 2021 at 7:12 pm
I know I've said this before in this thread. I can run the script from Powershell without any issues. The script isn't local to the PC. It's on a NAS share. Since I can run it off the NAS, I'm not going to copy it local. I doubt the script's location is the issue.
Since I cannot see what you see - I have to ask. How are you validating that it is actually working when you run it manually? The reason I pointed out running the script in Powershell ISE is that you can add breakpoints and walk through the execution of the code.
The reason I asked if you moved the file local is because that can have different execution policy requirements. It would eliminate whether the issue was caused by remote vs local file.
You stated it works for other packages - but are those other packages running this same script? Or are they running a different script?
A proper secure FTP process will require a fingerprint to allow logging in to the the sFTP server. That fingerprint can be different based on where it is executed. It could be an issue with logging into the server - but with no error logs this is just one more guess. It could be that specific sFTP server is running on a different port - but again, just guesses.
At this point - all that can be said is that the script is running and completing successfully. If the script is actually failing - but does not capture that failure correctly, it would return as successful without doing anything. Best guess here is that a cmdlet has been set to silently continue on error and there is an error being generated, but it just skips the error and continues.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
HA! SUCCESS!
So, I knew it wasn't running because the powershell script loads the file header information into a database table. And nothing was happening when I ran this in SSIS but it worked when I ran it manually. After talking with our local server genius about potentially remote signing the powershell script to see if that was the issue, he helped me check a few things.
First, was opening powershell without the ISE (which is what I was using for the manual run) and calling & "FilePath". It worked as I expected.
Second, something I did previously but forgot to mention, was testing the command in a regular CMD window. The CMD window would literally vanish without a line as soon as I hit enter. We changed the switches on the powershell command to include -NoLogo -NonInteractive -NoProfile and to change -Command to -File. Also, I had single quotes around the file name in SSIS because double-quotes were causing errors. So we changed that and the CMD window finally worked.
Finally, after forgetting that my configuration file was overwriting the package changes and the package failing to work 4 times before I did remember to fix the config file, I threw everything together in the config file AND IT WORKED.
New arguments line (not sure the new switches make a difference, I think -File and the double-quotes fixed the problem but I am NOT jinxing this) is...
-NoLogo -NonInteractive -NoProfile -windowstyle Hidden -ExecutionPolicy Bypass -ExecutionPolicy Unrestricted -File "\\My\nas\Share\App\Folder\My Powershell File Get.ps1"
As I have two different tasks using two different powershell scripts, I now need to fix the other one and verify it works as well. But … HAPPY DAY... and I hope this information helps someone else. -File, not -Command.
July 16, 2021 at 3:48 pm
YESYESYESYESYES... Second Execute Process task working as well!!!!
I had the wrong freakin' switch!
I hate it when I spend so much time trying to troubleshoot something and it turns out to be something so simple that I overlooked it. Sigh.
July 16, 2021 at 4:00 pm
YESYESYESYESYES... Second Execute Process task working as well!!!!
I had the wrong freakin' switch!
I hate it when I spend so much time trying to troubleshoot something and it turns out to be something so simple that I overlooked it. Sigh.
Sounds promising 🙂
Is the SSIS task waiting for completion before returning success? I may have been wrong in my original assessment of the disconnected execution, which has been my experience in the past. It may have been fixed in later versions or is process dependent...
I'm sure you'll do this, but for anybody else reading this I'd also suggest testing the following:
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply