December 27, 2015 at 1:52 pm
I've got some C# that checks a server via FTP to see if a file exists and then if it does it uses this expression
@[User::FTPRemoteFileExists] == true && @[User::FTPRemoteFileDate] > @[User::LastUpdate]
and then downloads the file.
This step errors when there is no file at all (eventhough I'm using the expression above that says that there has to be a file). Once a week (on Sundays) this file disappears and gets recreated hours later. I'm looking to see if the code can be modified in some way to not give the error
Source: Check if File Exists on FTP server and get modification date, then compare with LastUpdate Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Net.WebException: The remote server returned an error: (550) File unavailable (e.g., file not found, no access).
but just stop the job with no error (successful if you will). Can someone please give me direction?
Here's the C#. Any help is appreciated.
/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
// Added to use WebRequest.
using System.Net;
// Added to use NameValueCollection.
using System.Collections.Specialized;
namespace ST_196d0b476cbe434b9da8f5a3407b805b.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region Main - Original generated SSIS comments
#region VSTA generated code
enum ScriptResults {
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
#endregion //Main - Original generated SSIS comments
public void Main() {
// Get FTP Connection String from package variable.
string FTPConnS = Dts.Variables["FTPConnStr"].Value.ToString();
// Set up a Name Value collection to hold each of the FTP Connection parts.
NameValueCollection FTPConnColl = new NameValueCollection();
// Populate the Name Value collection from the Connection String variable.
string[] aryStrings = FTPConnS.Split(new char[] { ';' }, 4);
string[] nameAndValue;
foreach (string s in aryStrings) {
nameAndValue = s.Split(new char[] { '=' }, 2);
FTPConnColl.Add(nameAndValue[0], nameAndValue[1]);
}
// Grab FTP connection and file info from the FTP Name Value Collection.
string FTPServer = FTPConnColl["Server"];
string FTPPort = FTPConnColl["Port"];
string FTPUser = FTPConnColl["User"];
string FTPPwd = FTPConnColl["Pwd"];
// Assigning the value of LastUpdate below to have a value in the variable. If not > then failure
DateTime VarFTPRemoteFileDate = (DateTime)Dts.Variables["LastUpdate"].Value;
DateTime VarLastUpdate = (DateTime)Dts.Variables["LastUpdate"].Value;
// Grab the filename from a SSIS variable
string FTPRemoteFilePath = Dts.Variables["FTPRemoteFilePath"].Value.ToString(); ;
// Default file exists to false, and only set to true later if it's determined that the file actually exists.
Dts.Variables["FTPRemoteFileExists"].Value = false;
// Set up a FTP Web Request to grab the modified date of the file off of the FTP server
WebRequest FDRequest = FtpWebRequest.Create(string.Format("ftp://{0}:{1}@{2}:{3}/{4}", FTPUser, FTPPwd, FTPServer, FTPPort, FTPRemoteFilePath));
FDRequest.Proxy = null;
FDRequest.Method = WebRequestMethods.Ftp.GetDateTimestamp;
using (FtpWebResponse FDateResponse = (FtpWebResponse)FDRequest.GetResponse()) {
Dts.Variables["FTPRemoteFileExists"].Value = true;
Dts.Variables["FTPRemoteFileDate"].Value = FDateResponse.LastModified;
VarFTPRemoteFileDate = (DateTime)Dts.Variables["FTPRemoteFileDate"].Value;
//MessageBox.Show("Last Modified Date: " + VarFTPRemoteFileDate + System.Environment.NewLine +
// "LastUpdate: " + VarLastUpdate, "Title Bar", MessageBoxButtons.OKCancel, MessageBoxIcon.Asterisk);
}
//if (VarFTPRemoteFileDate > VarLastUpdate)
//{
// Dts.TaskResult = (int)ScriptResults.Success;
//}
//else
//{
// Dts.TaskResult = (int)ScriptResults.Failure;
//}
}
}
}
December 29, 2015 at 4:04 am
Curious if anyone's seen this or has any suggestions.
January 4, 2016 at 8:13 am
Just bumping this to see if anyone can take a look at this. Thanks!
January 4, 2016 at 5:56 pm
Are the variables using the script task setup to be "ReadWriteVariables" (yeah - kinda obvious but I gotta ask).
January 4, 2016 at 7:52 pm
These are read-only
User::FTPConnStr,User::FTPRemoteFilePath,User::LastUpdate
These are read-write
User::FTPRemoteFileDate,User::FTPRemoteFileExists
January 4, 2016 at 8:13 pm
I assume that the error you are getting is happening in the C# script task. There does not appear to be any error handling in the C# code. I am not a C# programmer so I don't know the exact syntax to use but try adding "TRY....CATCH" around the code that is attempting to get info from the remote server.
e.g.
try
{ // Default file exists to false, and only set to true later if it's determined that the file actually exists.
Dts.Variables["FTPRemoteFileExists"].Value = false;
// Set up a FTP Web Request to grab the modified date of the file off of the FTP server
WebRequest FDRequest = FtpWebRequest.Create(string.Format("ftp://{0}:{1}@{2}:{3}/{4}", FTPUser, FTPPwd, FTPServer, FTPPort, FTPRemoteFilePath));
FDRequest.Proxy = null;
FDRequest.Method = WebRequestMethods.Ftp.GetDateTimestamp;
using (FtpWebResponse FDateResponse = (FtpWebResponse)FDRequest.GetResponse()) {
Dts.Variables["FTPRemoteFileExists"].Value = true;
Dts.Variables["FTPRemoteFileDate"].Value = FDateResponse.LastModified;
VarFTPRemoteFileDate = (DateTime)Dts.Variables["FTPRemoteFileDate"].Value;
//MessageBox.Show("Last Modified Date: " + VarFTPRemoteFileDate + System.Environment.NewLine +
// "LastUpdate: " + VarLastUpdate, "Title Bar", MessageBoxButtons.OKCancel, MessageBoxIcon.Asterisk);
}
catch
{
// add error logic here if required
}
January 5, 2016 at 7:53 am
I'm not a C# programmer either. It will be a few days before I can get to it since I'm currently on the mend from surgery but I'll try..catch. What does try..catch do anyway?
January 10, 2016 at 5:02 pm
TRY...CATCH attempts to run the code in the TRY. If an error occurs, the code in the CATCH is executed.
You can have multiple CATCH blocks if you need to handle different errors. A catch block and have a condition associated with it which causes the code to be executed when the condition is TRUE. This allows you use different logic depending on the error that occurs.
January 11, 2016 at 4:44 am
You could set the read write variable (True if Success, False if Failure) and return the success constraint.
Then as the next step of the process check the variable using the Precedence Constraint Editor set to Expression and redirect dependent on the variable value.
Or use an Expression Task.
Hope this helps
David G
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply