November 13, 2023 at 9:49 pm
Hi,
I have a package with a Execute Process Task object that executes a PowerShell script that downloads a file from a tokenized URL, when the step is executed locally the task executes without with no problems but when the package is deployed to the SQL server and executed from the server the file from the URL that is being downloaded is not downloaded.
The URL acts like a file save as operation, you click on the link and you automatically receive a Save As window to save the file.
This is the tokenized URL that is being used:
This is the code that is being executed in the PowerShell script:
Invoke-WebRequest -Uri "https://www.SourceWebsite.com/user/?token=eyJhbGciOiJIUzI1NiIsInppcCI6IkRFRiJ9.eNoUkUuKnDEMhO_yrxPQ05Z6Z8nWPXqSWcwmNOkOBELuPhow-EFZ-qr073r-ebtuF0IKOMNhXOQqOOYaYhlKcmLj9e36eX_dWwlnFG3fyGoOMADWWCqzaAaPRYU1c-3NFHg4JaJ2mU_F3DN3rKG4YuZMTA40mjZg15yYB6OW6TloolSuenbuzAI8A10Pamng0gIPl6TBwYuPszAGQR22E1ILZHjIYiqynAY4nacaSwofBgsv3bSaBzBg-5iIjgvYB6oUkc86tK1doIGJCxnWF-8002YNmRNqn4iEo5nuhtvdtY1hO2lFrYZpmuQatY6cQTu44usqYLqbrE9LrbQ_df-OfdcQhb2GwXCrsPTaZiX7TMCyYgKJTqqLkAeKnt6W59F2qYrQaxrRUjLXDoB16lozjnJtstPgkR2jqVcusrnMe8g1iDArk3rSH89nD_rH--_X_ePX9_vj0W_vfx_XrZvDHKAALbq_rhsOF2VQpf-fAAAA__8.DdK8f6XTaMCjeuksi5RzxDW-5ZpkLn7oJQ2X9taQtoc&accessType=view_only" -OutFile "\\NetworkShare\Folder1\Folder2\HubList.csv"
The following are the settings in the Execute Process Task:
Executable = C:\Windows\SysWOW64\WindowsPowerShell\v1.0\Powershell.exe
Arguments = -ExecutionPolicy ByPass -command "\\NetworkShare\FolderOne\FolderTwo\FolderThree\HubDownload.ps1"
Working Directory = \\NetworkShare\FolderOne\FolderTwo\FolderThree
FailTaskIfReturnCodeIsNotSuccessValue = True
SuccessValue = 0
TimeOut = 0
TerminateProcessAfterTimeOut = True
WindowStyle = Normal
I am hoping some can give me an idea of why the file will download locally but won't download when the package has been deployed on the server.
A couple of notes:
1. I have confirmed that the job service account has the same access to the URL that I do locally.
2. I have confirmed that the package setting for server version is set correctly to be executed on SQL 2019.
Thanks for any light someone might be able to shed on this issue.
bw
November 13, 2023 at 10:19 pm
AV blocking powershell
Access to that networkshare not set correctly (both to the location of the csv file and to the PS script itself.)
you can check it by using a using a quick ssis package that does a "dir \\share\folder >> c:\temp\log.txt" to see results.
you should likely be executing the 64bit version instead of the 32 bit of powershell
November 14, 2023 at 11:25 am
Do you receive any error messages?
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
November 14, 2023 at 2:12 pm
Frederico, thank you for the information, I will check to make everything is running as expected and that the access is correct.
November 14, 2023 at 2:12 pm
Phil, we are not receiving an error message at all, the logs say that the package executed with no issues.
November 14, 2023 at 2:22 pm
This is likely to be a permissions issue. The user executing the command (which will be the SQL Agent service user, by default) needs appropriate access to all of the paths mentioned in the code.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply