May 10, 2010 at 10:17 pm
Comments posted to this topic are about the item No SFTP Task Component in SSIS 2005/2008? No Problem!
May 10, 2010 at 10:52 pm
Cool article. I like CozyRoc's SFTP task too
May 11, 2010 at 12:40 am
I am using a similar SFTP 3rd party application in one of my ODS loading SSIS package.
The problem is that the error handling is generalized and not specific.
I suggest searching for dlls that can be called through script task which can pass error messages to package variable which can be included in the sent mail task. You can also compare already downloaded files against your audit table there by avoiding duplicate downloads.
May 11, 2010 at 2:08 am
Nice article, thanks for sharing Stan. I very recently sat with the same issue, and after some investigation into different solutions to the issue I decided to use Core FTP light. I added variables to my SSIS package, configured an Execute Process task that passed the valid commands to the executable (via variables), added file archiving steps to ensure good housekeeping, and voila! Bob's your uncle. Worked like a charm. No extra coding required (other than basic variable expressions) 🙂
May 11, 2010 at 7:12 am
May 11, 2010 at 7:44 am
What concerns me about this approach is that you leave a batch file hanging around that contains your password. You have to be very careful in your cleanup and error handling routines so that you don't leave the auto-generated batch file sitting in your working directory.
May 11, 2010 at 7:47 am
"You have to be very careful in your cleanup and error handling routines so that you don't leave the auto-generated batch file sitting in your working directory."
That's why the last section of code deletes the batch file and the sftp script.
May 11, 2010 at 8:06 am
Good article, I have been using bitvise's product sftpc.exe which has very good logging giving me the ability to not only use the SSIS embedded start process task but I can start it with a logging exe to produce transfer log files for each file which are in turn emailed to distribution lists.
Within the Start process task I have setup two Expressions to set the Arguments and Executable.
Arguments - @[User::SFTPC_LOG_ARGS] + " " + @[User::SFTPC_LOG_FILE_PATH] + " " + @[User::SFTPC_LOCATION] + " " + @[User::UserName] + "@" + @[User::Destination] + ":" + @[User::Port] + " -pw=" + @[User::Password] + " " + @[User::MD5_BB] + " -cmd=" + @[User::Command]
Executable - @[User::SFTPC_LOG_LOCATION]
May 11, 2010 at 8:08 am
Good article.
Having that username/password in a batch file is an issue. If (for any reason, like the FTP server is down) the package fails, that file could be left behind, and then anyone could see it. You might be deleting it in the last step, but if the package fails before that step then that last step won't get run.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 11, 2010 at 9:05 am
Good article. I have in the past used a simalar solution with Winscp, however we recently change some of our poliacys and the SSIS packagewas moved to a server where 3rd party apps where forbiden. To get around this i found out that you could easly use the c# ftp class in a script task to do the same thing & it's more relable, and faster cause the server doesn't have to fire up another application.
//Get Package Varables
String MyFTPSiteName = Dts.Variables["User::FTPHost"].Value.ToString();
String MyFTPLogin = Dts.Variables["User::FTPUser"].Value.ToString();
String MyFTPPassWord = Dts.Variables["User::FTPPassWord"].Value.ToString();
String MyFolder = Dts.Variables["User::Folder"].Value.ToString();
String MyPeriod = Dts.Variables["User::Period"].Value.ToString();
String MyFileName = Dts.Variables["User::File"].Value.ToString();
//Accept TLS/SSL Certificate
ServicePointManager.ServerCertificateValidationCallback = new System.Net.Security.RemoteCertificateValidationCallback(AcceptAllCertificatePolicy);
//Upload Data File
{
FtpWebRequest MyRequest = FtpWebRequest.Create(MyFTPSiteName + "/" + MyFileName ) as FtpWebRequest;
MyRequest.EnableSsl = true;
MyRequest.Credentials = new NetworkCredential(MyFTPLogin, MyFTPPassWord);
MyRequest.Method = WebRequestMethods.Ftp.UploadFile;
//Read File into byte array
FileInfo MyFileI = new FileInfo(MyFolder + "\\"+MyFileName );
FileStream MyFStream = new FileStream(MyFolder + "\\" + MyFileName, FileMode.Open, FileAccess.Read);
BinaryReader MyReader = new BinaryReader(MyFStream);
byte[] MyFile = MyReader.ReadBytes(int.Parse(MyFileI.Length.ToString()));
MyReader.Close();
MyFStream.Close();
MyRequest.ContentLength = MyFile.Length;
//Upload File
Stream MyStream = MyRequest.GetRequestStream();
MyStream.Write(MyFile, 0, MyFile.Length);
MyStream.Close();
FtpWebResponse MyResponse = MyRequest.GetResponse() as FtpWebResponse;
MyResponse.Close();
}
public bool AcceptAllCertificatePolicy(object sender, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors)
{
return true;
}
May 11, 2010 at 10:26 am
awesome. this was something that I was looking for. Adding 3rd party apps to my ETL is a little mickey mouse for me.
Thank You very much.
May 11, 2010 at 4:10 pm
Nice article.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 12, 2010 at 3:01 am
An alternative method would be to set up a "virtual" FTP server using stunnel (www.stunnel.org) pointing to the remote sftp server, which you then connect using normal ftp uploads, and sftp handles the SSL element.
May 12, 2010 at 7:50 am
Interesting article, but I chose a slightly different path in handling things. I am using the portable version of WinSCP, meaning there is no installation needed. They also expose Command Line arguments, meaning that all the stuff stored in the batch file is passed along as variables in the package. So I can store the variables in a database somewhere, call them when the package loads, then pass them along to the command line options. Consider the following:(written in C#...)
//Attaching the debugger in a 64-bit instance of SSIS will not work.
//MessageBox.Show("Attach the Debugger", "Attach the Debugger");
// TODO: Add your code here
ScriptResults TaskResult;
//jf 04132010 Created objects to hold variables set at package level
String WinSCPPath = Dts.Variables["WinSCPPath"].Value.ToString();
String WinSCPCom = Dts.Variables["WinSCPCom"].Value.ToString(); ;
String WinSCPini = Dts.Variables["WinSCPini"].Value.ToString();
String WinSCPLoggingPath = Dts.Variables["WinSCPLoggingPath"].Value.ToString();
String WinSCPLogFileName = Dts.Variables["WinSCPLogFileName"].Value.ToString();
String LoginName = Dts.Variables["LoginName"].Value.ToString();
String LoginPassword = Dts.Variables["LoginPassword"].Value.ToString();
String SiteURI = Dts.Variables["SiteURI"].Value.ToString();
String HostKey = Dts.Variables["HostKey"].Value.ToString();
String FileToPush = Dts.Variables["FileToPush"].Value.ToString();
String UploadDirectory = Dts.Variables["UploadDirectory"].Value.ToString();//UploadDirectory
//jf 04132010 Created object to hold reference for FireAgain of the FireProgress Control
bool fireAgain = false;
try
{
//jf 04132010 Attach winscp to the process within the Package
System.Diagnostics.Process winscp = new System.Diagnostics.Process();
Dts.Events.FireProgress("WinSCP process Created", 0, 0, 100, "WinSCP", ref fireAgain);
//jf 04132010 Pass variables into the Process object
//Tell the Process where to find the WinSCP object for loading
winscp.StartInfo.FileName = String.Format("{0}{1}"
, WinSCPPath
, WinSCPCom);
//Pass the location of the ini file and the logging xml file to the Process
winscp.StartInfo.Arguments = String.Format("/log={3}{1}"
, WinSCPPath
, WinSCPLogFileName
, WinSCPini
, WinSCPLoggingPath);
Dts.Events.FireProgress("Assigned Logging File", 10, 0, 100, "WinSCP", ref fireAgain);
//Do not launch a new shell "cmd" window
winscp.StartInfo.UseShellExecute = false;
//Redirect the input and output to the Logging Component
winscp.StartInfo.RedirectStandardInput = true;
winscp.StartInfo.RedirectStandardOutput = true;
//Do not show a cmd window
winscp.StartInfo.CreateNoWindow = true;
//Start the Process Thread
winscp.Start();
Dts.Events.FireProgress("WinSCP process Launched", 20, 0, 100, "WinSCP", ref fireAgain);
//Pass configuration information to WinSCP
winscp.StandardInput.WriteLine("option batch abort");
winscp.StandardInput.WriteLine("option confirm off");
//Pass Login information to WinSCP for Authentication
winscp.StandardInput.WriteLine(String.Format("open {0}:{1}@{2} -hostkey=\"{3}\""
, LoginName
, LoginPassword
, SiteURI
, HostKey));
Dts.Events.FireProgress("Remote SFTP Connection Established", 40, 0, 100, "WinSCP", ref fireAgain);
//Execute WinSCP Commands
winscp.StandardInput.WriteLine("ls");
//--change to the needed directory
winscp.StandardInput.WriteLine(String.Format("cd {0}", UploadDirectory));
Dts.Events.FireProgress("Remote SFTP File Action", 60, 0, 100, "WinSCP", ref fireAgain);
//--List the directory
winscp.StandardInput.WriteLine("ls");
//--push the file to the server
winscp.StandardInput.WriteLine(String.Format("put {0}", FileToPush));
Dts.Events.FireProgress("Process Finished", 80, 0, 100, "WinSCP", ref fireAgain);
Dts.Events.FireProgress(String.Format("put {0}", FileToPush), 0, 0, 0, "WinSCP", ref fireAgain);
//Close input to the file
winscp.StandardInput.Close();
Dts.Events.FireProgress("Remote SFTP Disconnected, WinSCP Closed", 100, 0, 100, "WinSCP", ref fireAgain);
//wait for WinSCP to Exit
winscp.WaitForExit();
MessageBox.Show(winscp.StandardOutput.ReadToEnd(), "WinSCP Output");
TaskResult = ScriptResults.Success;
}
catch (Exception e)
{
//If there is problem, fail the event and output the exception Message...
Dts.Events.FireError(999, e.Message, "WinSCP", string.Empty, 0);
TaskResult = ScriptResults.Failure;
//MessageBox.Show(e.Message);
}
Dts.TaskResult = (int)TaskResult;
Dts.TaskResult = (int)TaskResult;
Notice that we can also fire events that bubble up a message within the context of the package. This means no third party installation on the server, no batch file to be overwritten and most important of all, everything is saved in a script task in the package.
May 12, 2010 at 8:44 am
Pretty cool.
You should submit that as an article. I did what I did because it's the only thing I could figure out.
Your way is better.
Viewing 15 posts - 1 through 15 (of 60 total)
You must be logged in to reply to this topic. Login to reply