Excecuting a Powershell script within a SSIS package
The Powershell script goes to a website, grab some information and writes it on a flat file. If I run the script within Powershell it works perfectly, but when I run it from SQL Server Agent, it fails. I have already given read/write permisions in the server to the SQL Server Agent user who runs the package. I also have tried creating a Step Type: PowerShell and just run the script from within the job; Operating system (CmdExec) excecuting powershell comand with the file name as an argument; Operating system (CmdExec) running a batch file which calls the Powershell script.
This is the powershell script:
$ie = New-Object -ComObject 'internetExplorer.Application'
$ie.Visible= $false
$ie.Navigate("https://www.frbservices.org/EPaymentsDirectory/fpddir.txt")
while ($ie.Busy -eq $true){Start-Sleep -seconds 1;}
$Link=$ie.Document.getElementsByTagName("button") | where-object {$_.value -eq "Agree"}
If($Link){
$Link.click()
}
while ($ie.Busy -eq $true){Start-Sleep -seconds 1;}
$destination = ".FDRTestFileFDRFile.txt"
$content = (@($ie.Document.getElementsByTagName("pre"))[0].innerText | Out-
File $destination)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($ie)
Remove-Variable ie
I'm getting this error when the job finishes:
New-Object : Creating an instance of the COM component with CLSID {0002DF01-0000-0000-C000-000000000046} from the IClassFactory failed due to the following error: 80004005 Unspecified error (Exception from HRESULT: 0x80004005 (E_FAIL)). At D:SSISFDRDownloadFDRFile.ps1:1 char:7 + $ie = New-Object -ComObject 'internetExplorer.Application' + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ResourceUnavailable: (:) [New-Object] COMExcept ion + FullyQualifiedErrorId : NoCOMClassIdentifiedMicrosoft.PowerShell.Comman ds.NewObjectCommandProperty 'Visible' cannot be found on this object; make sure it exists and is settable. At D:SSISFDRDownloadFDRFile.ps1:3 char:1 + $ie.Visible= $true + ~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [] RuntimeException + FullyQualifiedErrorId : PropertyNotFoundYou cannot call a method on a null-valued expression. At D:SSISFDRDownloadFDRFile.ps1:4 char:1 + $ie.Navigate("https://www.frbservices.org/EPaymentsDirectory/fpddir.txt") + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [] RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull You cannot call a method on a null-valued expression. At D:SSISFDRDownloadFDRFile.ps1:8 char:1 + $Link=$ie.Document.getElementsByTagName("button") | where-object {$_.value -eq " ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~ + CategoryInfo : InvalidOperation: (:) [] RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull You cannot call a method on a null-valued expression. At D:SSISFDRDownloadFDRFile.ps1:17 char:15 + $content = (@($ie.Document.getElementsByTagName("pre"))[0].innerText | Out-File ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [] RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull Exception calling "ReleaseComObject" with "1" argument(s): "Object reference not set to an instance of an object." At D:SSISFDRDownloadFDRFile.ps1:18 char:1 + [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ie) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [] MethodInvocationException + FullyQualifiedErrorId : NullReferenceException Remove-Variable : Cannot find a variable with name 'ie'. At D:SSISFDRDownloadFDRFile.ps1:19 char:1 + Remove-Variable ie + ~~~~~~~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: (ie:String) [Remove-Variable] I temNotFoundException + FullyQualifiedErrorId : VariableNotFoundMicrosoft.PowerShell.Commands.R emoveVariableCommand. Process Exit Code 0. The step succeeded.,00:00:02,0,0,,,,0
Any ideas why this is happening?
链接地址: http://www.djcxy.com/p/29090.html