August 22, 2011 at 2:00 pm
The following script task uploads files from a local UNC to an UNIX location through sftp. We are making use of a tool, PUTTY-SFTP, (PSFTP) to accomplish the task.
The psftp exe expects a .src (source) file which contains the command to be executed on the UNIX box (for eg. mput). A batch file is created that makes a call to the psftp exe with UNIX Server name, user id, password and script file pathas parameters.
The UNIX server name, userid, pwd etc ae stored and retrieved from global variables.
The problem is the package runs fine from the BI IDE, but fails to take off when scheduled as a job. It throws back an 'access denied' error, at the step highlighted (in red) below.
The job is configured to run under a proxy context and the credential, being a local admin, has full access on the UNC, from where the files are picked up from. The UNIX user has read write permission on the shared mountpoint. I doubt it's something to do with calling the psftp EXE.
The funny part is it ran successfully twice after the proxys were set up, but failing since then.
Will greatly appreciate any guidance in this regard, as the isssue has started to trigger some panic.
Kind Regards
Private Sub psFTPToUNIX()
'Load global variables with values
Dim PathToBatch_bat As String = Dts.Variables("psftp_batchfile").Value.ToString
Dim PathToPsftp_exe As String = Dts.Variables("psftp_exefile").Value.ToString
Dim PathToScript_sc As String = Dts.Variables("psftp_sourcefile").Value.ToString
Dim ServerName As String = Dts.Variables("ftp_server").Value.ToString
Dim ServerUserName As String = Dts.Variables("ftp_user").Value.ToString
Dim ServerPassword As String = Dts.Variables("PWD").Value.ToString
Dim ServerSubdirectory As String = Dts.Variables("RemoteDir").Value.ToString
Dim SourceFilePath As String = Dts.Variables("sourcefiles").Value.ToString
'Create dynamic content of script file with local variable values
Dim commands As String
commands += "cd " & ServerSubdirectory & Chr(13) & Chr(10)
commands += "mput " & SourceFilePath & Chr(13) & Chr(10)
commands += "quit"
'Write script file
Dim oFile As System.IO.File
Dim oWrite As System.IO.StreamWriter
oWrite = oFile.CreateText(PathToScript_sc)
oWrite.WriteLine(commands)
oWrite.Close()
'Create command line to run psftp in this format: psftp.exe sftp.server.domain -l username -p password -b script_file_path
Dim command_line As String = PathToPsftp_exe & " " & ServerName & " -l " & ServerUserName & " -pw " & ServerPassword & " -b " & PathToScript_sc '& " -batch"
'Write batch file
oWrite = oFile.CreateText(PathToBatch_bat)
oWrite.WriteLine(command_line)
oWrite.Close()
'Run batch file as system process
Dim startInfo As System.Diagnostics.ProcessStartInfo
Dim pStart As New System.Diagnostics.Process
startInfo = New System.Diagnostics.ProcessStartInfo(PathToBatch_bat)
pStart.StartInfo = startInfo
pStart.Start()
pStart.WaitForExit()
August 24, 2011 at 2:22 am
A little update. I created the proxy on my domain credential which was not included in the local administrator group. Adding the same in that privileged category did make the job run successfully.
My confusion now is, when my domain credential is not included in the admin group, running from BI IDE (the job should be using my credentials now) still takes the package to success . However, when scheduled as a job (the job still uses the same credentials, as implemented through proxy) the same fails (no need to say, the same succeeds when my ID is included the Admin group). The problem step is a call to an outside EXE (psftp.exe).
How it is different, when the EXE is called (in a script task) from the BI IDE, and as a job (seemingly using the same domain credential). What is happening behind the scene ? Why the doamin needs to be added to the ADMINISTRATIVE group in the later case please?
Any advice in this regard will be highly appreciated.
Regards
August 25, 2011 at 12:11 am
Any words of wisdom on this please?
Regards
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply