July 1, 2009 at 7:55 am
nsoftware SSIS task pack provide the sftp capability for SSIS.
August 26, 2009 at 5:00 pm
if anyone knows of contract doing (c#, .net, asp.net,biztalk, etc.) in seattle area please let me know.
Sorry for shameless self-promotion 😉
Below is another way to skin the cat for ssis,sftp, putty(psftp) using a script task.
public void Main()
{
System.Diagnostics.Process proc = new System.Diagnostics.Process();
try
{
//create file to be sftp'd
CreateFile();
//initialize process configuration
proc.StartInfo.FileName = @"C:\Program Files\Putty\psftp.exe";
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.CreateNoWindow = true;
proc.StartInfo.RedirectStandardError = true;
proc.StartInfo.RedirectStandardInput = true;
proc.StartInfo.RedirectStandardOutput = true;
proc.EnableRaisingEvents = true;
//set SFTP args
proc.StartInfo.Arguments = Dts.Variables["User::SFTPServer"].Value.ToString().Trim();
proc.StartInfo.Arguments += " -l " + Dts.Variables["User::SFTPUser"].Value.ToString().Trim();
proc.StartInfo.Arguments += " -pw " + Dts.Variables["User::SFTPPswd"].Value.ToString().Trim();
//start process - connect to sftp server
proc.Start();
//Set StandardInput autoflush
proc.StandardInput.AutoFlush = true;
System.Threading.Thread.Sleep(2000); //added wait to allow time for SFTP server to respond.
//putty prompts if you would like to store the fingerprint key to putty cache. sending 'n' for no
proc.StandardInput.WriteLine("n");
//send commands to put file
proc.StandardInput.WriteLine("put " + Dts.Variables["User::FileFolder"].Value.ToString() +
Dts.Variables["User::FileOutName"].Value.ToString()); //send file
proc.StandardInput.WriteLine("bye"); //disconnect
proc.WaitForExit(2000); // wait 2 seconds
//get messages from standarderror and standardouput - note: for debugging puposes.
string serr = proc.StandardError.ReadToEnd();
string sout = proc.StandardOutput.ReadToEnd();
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception)
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
finally
{ //cleanup resources
proc.Dispose();
}
}
private void CreateFile()
{
//create filename
Dts.Variables["User::FileOutName"].Value = @"\myfile" + DateTime.Now.Day + DateTime.Now.Hour + DateTime.Now.Minute + ".txt";
//create filestream with handle of file
FileStream fs = new FileStream(Dts.Variables["User::FileFolder"].Value.ToString() +
Dts.Variables["User::FileOutName"].Value.ToString(), FileMode.Create, FileAccess.Write, FileShare.None);
// Create a new stream to write to the file
StreamWriter sw = new StreamWriter(fs);
// Write a string to the file
sw.Write(Dts.Variables["User::strips"].Value);
// Close StreamWriter
sw.Close();
// Close file
fs.Close();
}
August 26, 2009 at 6:46 pm
I've got a video on SQLShare.com (formerly JumpstartTV) to demonstrate how to process SFTP files in SSIS using freeware components. You can watch the video here.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
February 26, 2010 at 2:58 am
Problem:
I need to send a file from my server to other server using SFTP.
I have included sftp script in a.cmd file and added an "execute process task" in a DTS. But when ever I run the DTS either through designer, or through a SQL job , the SFTP step gets failed and gives "Connecting to Host failed" and "The process exit code was "1" while the expected was "0". End Error DTExec" error.
In my server SSH tectia Server and SSH Tectia Client is installed.
I have accepted the public host key for that destination server in interactive mode(through command prompt).
When I run the script seperately outside DTS, file is sent to the destination. But when used as a step in DTS, it fails abruptly. Please help to get rid of this problem as it is going bad....
I have tried changing the ownership of DTS, but still it is not working.
The user I accepted the host key and the user executing SQL job are same.
Please help.
February 26, 2010 at 7:27 pm
I would suspect that the problem is most likely in the command line params you are passing to your executable are incorrect. Try putting the same comand line params into a batch file and run it manually from the command line. Check also the documentation for the secure FTP application you are using - they may have some specific syntax tips for you.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
March 1, 2010 at 1:20 am
Hi Tim,
Thanks for the post.
When I run my batch script manually it is getting executed successfully, problem is when I automate the same script in a DTS (SQL Server 2000), it gets failed.
Both the syntax and parameters are same.
sftpg3 -v --password=pwd -B %scriptpath%TectiaGMAC.scr username@hostname#portnum
March 1, 2010 at 6:44 am
Is the script that you're running located on a remote machine, and if so, are you accessing via a mapped drive? If so, try changing to a UNC path (\\servername\directory\myscript.bat).
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
March 1, 2010 at 6:54 am
Hi Tim,
The scripts are in the same server location where I am running the scripts.
Such as I have placed the scripts under E:\APPS\.. of the server.
And referred the same path in DTS as well. But it gives me error when run through DTS.
Thanks
March 25, 2010 at 12:23 pm
You can use the free FTP Task++ from http://www.easkills.com/ssis/ftptask
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply