|
|
|
SSC Journeyman
      
Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 78,
Visits: 597
|
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Tuesday, February 07, 2012 12:31 PM
Points: 6,
Visits: 103
|
|
| Cool article. I like CozyRoc's SFTP task too
|
|
|
|
|
Grasshopper
      
Group: General Forum Members
Last Login: Tuesday, August 14, 2012 1:10 PM
Points: 15,
Visits: 113
|
|
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.
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Monday, June 18, 2012 4:11 AM
Points: 27,
Visits: 219
|
|
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)
|
|
|
|
|
SSC-Addicted
      
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 8:10 AM
Points: 445,
Visits: 840
|
|
I've been doing this with DTS since SQL7. One catch I don't see mentioned is that you have to log in manually the first time to cache the key. Otherwise the process hangs waiting for the response from the user to verify the key. This can also be a problem when the key gets changed.
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:32 PM
Points: 60,
Visits: 241
|
|
| 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.
|
|
|
|
|
SSC Journeyman
      
Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 78,
Visits: 597
|
|
"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.
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Monday, April 29, 2013 6:32 AM
Points: 1,
Visits: 131
|
|
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]
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 6,367,
Visits: 8,227
|
|
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 If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it! Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines, CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Monday, December 19, 2011 11:57 AM
Points: 8,
Visits: 83
|
|
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; }
|
|
|
|