question

smithj338 avatar image
smithj338 asked

need help creating a SSIS script task for ftp to receive files (script added)

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
ssisftpvbscript
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
tomgough79 avatar image
tomgough79 answered
Can you use the "Execute Process" task? If so, you might be able to set something like this up: Create a file containing the ftp commands you need. it will look something like: user ascii quote site namefmt 1 cd .. get /ftp/ED1160/sales/*.PARTS Then set up the execute process task to run the following: ftp -s:ftpcommands servername Where ftpcommnds is the name of the file you create above. Also, if you are trying to pull back multiple files, you may well need to use mget, rather than get
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'd be willing to try anything. I'm not sure that I will be allowed to store a file on that server though or allowed to put sign on info in a unsecured file. Thanks for the info on the mget and your help. It's appreciated!
0 Likes 0 ·
Another thought might be to look at alternative ftp clients that you could run using the Execute Process task and could run some sort of saved command set. But I guess if you're going to have issues storing a file, you'll have an even bigger battle installing new software. Is there any way that you can transfer the files to a Windows share without using SSIS and then pick them up from there with SSIS? (I'm guessing not otherwise that might have been the easy option to start with)
0 Likes 0 ·
Thanks for the reply. I think the SAP ABAP programs can only put the flat files on a share on the IBM Power7 or at least that's the only way our Basis guy knows how to do it.
0 Likes 0 ·
Thanks for cleaning this up Thomas. I was sure you'd have a solution/suggestion for this.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.