sftp and ssis

Question

Post reply

sftp and ssis

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

skybluegbt Grasshopper Points: 17 More actions · Answer 1

nsoftware SSIS task pack provide the sftp capability for SSIS.

http://www.nsoftware.com/ssis/tasks/sftp.aspx

brady-786864 Old Hand Points: 392 More actions · Answer 2

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();

}

Tim Mitchell SSCoach Points: 15685 More actions · Answer 3

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
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

parameshwari.vasanthakumar SSC Enthusiast Points: 116 More actions · Answer 4

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.

Tim Mitchell SSCoach Points: 15685 More actions · Answer 5

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
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

parameshwari.vasanthakumar SSC Enthusiast Points: 116 More actions · Answer 6

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

Tim Mitchell SSCoach Points: 15685 More actions · Answer 7

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
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

parameshwari.vasanthakumar SSC Enthusiast Points: 116 More actions · Answer 8

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

Ahmad Alkilani SSC Enthusiast Points: 127 More actions · Answer 9

You can use the free FTP Task++ from http://www.easkills.com/ssis/ftptask