question

I normally use the FTP Task in SSIS but I'm trying to connect to an IBM Power7 running UNIX and SAP. The reason I can't use the FTP task is because I have to change a FTP attribute on the IBM to allow me to go at it by path instead of library. I've found some scripts out there but can't figure out how to enter the attribute and then change directory to the root. Below are the ftp commands I need to enter and then receive the file(s). We changed the keyword NAMEFMT on the IBM command CHGFTPA to *PATH which should have allowed using the FTP task but i can't get it to point to the root. I'm by no means an expert at SSIS but more of a casual user and I've never written a script which is why I need help. I've spent several days just finding out this information. I would greatly appreciate any help provided. I need the commands immediately below put in the script. I don't know VB so I'm not sure if the code is missing anything. ftp 10.132.29.31 user password ascii quote site namefmt 1 cd .. get /ftp/ED1160/sales/*.PARTS Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime _ _ Partial Public Class ScriptMain Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase Enum ScriptResults Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure End Enum Public Sub Main() { string serverName = Dts.Variables ["User::FTPServerName"].Value.ToString(); string remotepath = Dts.Variables ["User::FTPDestinationPath"].Value.ToString(); string userID = Dts.Variables ["User::FTPUserID"].Value.ToString(); string password = Dts.Variables ["User::FTPPassword"].Value.ToString(); string sourcePath = Dts.Variables ["User::SourcePath"].Value.ToString(); ' PrivateFunction FtpPut(ByVal serverName AsString, ByVal userID AsString, ByVal password AsString, ByVal sourcePath AsString, ByVal destinationPath AsString) AsString Dim result AsString = String.Empty Dim sourceFile AsString = Path.Combine(sourcePath, "StagingData.txt") Dim batchFilePath AsString = Path.Combine(sourcePath, "FTPBatch.txt") Dim sb As StringBuilder = New StringBuilder() sb.Append("open " + serverName + Environment.NewLine) sb.Append(userID + Environment.NewLine) sb.Append(password + Environment.NewLine) sb.Append("ascii" + Environment.NewLine) sb.Append("quote site conddisp=delete cylinders primary=30 secondary=20 recfm=fb" + Environment.NewLine) sb.Append("quote site lrecl=1000" + Environment.NewLine) sb.Append("put ") sb.Append("""" + sourceFile + """") sb.Append(" '" + destinationPath + "'" + Environment.NewLine) sb.Append("close" + Environment.NewLine) sb.Append("bye" + Environment.NewLine) Dim realBatchText AsString = sb.ToString() 'create the batch file. Dim realBatchTextBytes AsByte() = New UTF8Encoding(False, True).GetBytes(realBatchText) Using writer As Stream = New FileStream(batchFilePath, FileMode.Create, FileAccess.Write, FileShare.None) writer.Write(realBatchTextBytes, 0, realBatchTextBytes.Length) EndUsing() 'Process Info to ftp and run Batch File created Dim process AsNew Process With Process With .StartInfo .FileName = "ftp.exe" .Arguments = String.Format("-s:\"" + batchFilePath + """) .CreateNoWindow = True .UseShellExecute = False .WindowStyle = ProcessWindowStyle.Hidden .RedirectStandardOutput = True EndWith .Start() result = .StandardOutput.ReadToEnd().ToString() .Close() EndWith() File.Delete(batchFilePath) Return result EndFunction() ' Dts.TaskResult = ScriptResults.Success End Sub End Class