Powershell Script in SSIS not downloading the file when run from SQL Server Agent

Question

Powershell Script in SSIS not downloading the file when run from SQL Server Agent

kashyap4007

SSC Eights!

Points: 859
More actions
December 18, 2018 at 11:27 am

#416567

/*
Hi, I have an SSIS Package which uses the Execute Process task to run a PowerShellScript.
The PowerShell Script just downloads a file from the Internet.
I ran from the Visual Studio and everything is running well.
I deployed it on the Server, Scheduled a SQL Server Agent Job and ran from there and It runs good from there but I don't see any file getting downloaded.
Not sure where it is going wrong since I don't see any error.
Below Pic/ Code will show how I did. Can you please tell me what is going on here.
FYI... My power shell Script
param (
[string]$filename = "AnnualProductionSummaryData.zip"
)
Start-BitsTransfer -source "http://cogcc.state.co.us/documents/data/downloads/production/co%202018%20Annual%20Production%20Summary-xp.zip" -destination "\\TILOKDPSQLS73\WellData\Temp\Temp_Delete\$filename"
Pics:

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 1

Are you sure the service account has access to the directories? You can try adding this to the end of the script to see if any errors are actually being raised, just set the $log_dir variable to a path the service account definitely has access to.

If($error.Count -ge 1)
{
If(Test-Path $log_dir)
{
Remove-Item $log_dir
}
$error.ForEach({(Add-Content $log_dir ($_.ToString() + "`n" + $_.Exception + "`n" + $_.ErrorDetails.Message + "`n" + $_.InvocationInfo.PositionMessage + "`n"))})
}

kashyap4007 SSC Eights! Points: 859 More actions · Answer 2

Thank you Zartin.
I got the below error when I appended your code.
Sorry I cannot understand this. Can you help me with this?

The operation being requested was not performed because the user has not logged on to the network. The specified service does not exist. (Exception from HRESULT: 0x800704DD)
System.Runtime.InteropServices.COMException (0x800704DD): The operation being requested was not performed because the user has not logged on to the network. The specified service does not exist. (Exception from HRESULT: 0x800704DD)
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
at Microsoft.BackgroundIntelligentTransfer.Management.Interop.Job.Cancel()
at Microsoft.BackgroundIntelligentTransfer.Management.NewBitsTransferCommand.ProcessRecord()
at System.Management.Automation.CommandProcessor.ProcessRecord()

At \\TILOKDPSQLS73\WellData\Temp\Temp_Delete\downloadfileParam.ps1:6 char:1
+ Start-BitsTransfer -source "http://cogcc.state.co.us/documents/data/d ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 3

What kind of authentication does the source require?

kashyap4007 SSC Eights! Points: 859 More actions · Answer 4

I am trying to download a zipped file from a state website. ( It is a public website and anyone can get it.)
The source file path is:
http://cogcc.state.co.us/documents/data/downloads/production/co%202018%20Annual%20Production%20Summary-xp.zip

Did I answer your question Zartin?

frederico_fonseca SSCoach Points: 16225 More actions · Answer 5

SQL Server agent needs to run as a Domain user most likely - and with access to the internet (which in may companies is blocked on the servers)

kashyap4007 SSC Eights! Points: 859 More actions · Answer 6

I do have access to the Internet on the Server Frederico.
I am running the Job using a proxy account.

frederico_fonseca SSCoach Points: 16225 More actions · Answer 7

ahhh.. bitstransfer - there is a know issue with that.
see https://stackoverflow.com/questions/22457548/bits-transfer-file-with-multi-domain
although not exactly the same situation it is the most likely reason

you will need to adapt your code

kashyap4007 SSC Eights! Points: 859 More actions · Answer 8

Thank you frederico/Zartin.
I was able to get it using the below link. (Was able to get it done using Method1 and Methond2)
https://blog.jourdant.me/post/3-ways-to-download-files-with-powershell

Powershell Script in SSIS not downloading the file when run from SQL Server Agent

Cookies on SQLServerCentral