Scenario
Recently there was an issue faced in one of our projects on a file processing system that we designed. The file processing system was designed to process third party files which had to be downloaded from a FTP location.
We designed a SSIS package to do the FTP download and file processing and used WinSCP for the purpose. The logic used was as per one of my earlier articles as given below
On executing the package the Execute Process Task which was added to do the FTP has failed. As SSIS doesn't give too much information on the error I put a break point on the pre execute event of the execute process task and captured the full statement which is to be executed by WinSCP.
I launched a command prompt window and tried executing the statement generated by the SSIS and got the below
As seen from the above screenshot I got an access denied error. I checked the credentials again and tried them on WinSCP client installed in my machine and to my surprise it was working fine allowing me to connect to the FTP location. From this I could infer that the problem was with the way SSIS or the WinSCP command line is handling the credentials.
I did some detailed analysis on the command used for FTP operation by WinSCP which was as below
open ftp://username:password@Servername:Port
And managed to find the issue. The problem in this case was the password which contains special characters like +. This will break in case of the WinSCP.
Solution
The solution in the above case was to encode the special characters before being passed to the url. If you've used .NET programming before you should know that there's a function available in .NET EscapeDataString which is available in System.Uri namespace. This function provides with the encoded values when special character is passed.
Based on this we can develop a solution such as below for handling the above scenario.
Create a function which will parse through the passed URL string and looks for special characters within it. Once found it will pass the character to the EscapeDataString function which will return the encoded value corresponding to it.
The string parsing can be done using RegexMatch function to find out the special characters.
So the overall package would look like this
public void Main()
{
var str = EncodeUrl(Dts.Variables["Url"].Value.ToString());
Dts.Variables["EncodedUrl"].Value = str;
Dts.TaskResult = (int)ScriptResults.Success;
}
private static string EncodeUrl(string URLInput)
{
const string pattern = "[$&+,/:;=?@]";
var match = Regex.Match(toEncode, pattern);
while (match.Success)
{
URLInput = URLInput.Replace(match.Value, Uri.EscapeDataString(match.Value));
match = Regex.Match(URLInput, pattern);
}
return URLInput;
}
The string parsing can be done using RegexMatch function to find out the special characters.
So the overall package would look like this
The Script task will receive as parameter the URL for FTP/SFTP and then parse it for encoding the special characters as below
public void Main()
{
var str = EncodeUrl(Dts.Variables["Url"].Value.ToString());
Dts.Variables["EncodedUrl"].Value = str;
Dts.TaskResult = (int)ScriptResults.Success;
}
private static string EncodeUrl(string URLInput)
{
const string pattern = "[$&+,/:;=?@]";
var match = Regex.Match(toEncode, pattern);
while (match.Success)
{
URLInput = URLInput.Replace(match.Value, Uri.EscapeDataString(match.Value));
match = Regex.Match(URLInput, pattern);
}
return URLInput;
}
Once the encoded URL is returned by the above function you can then store it in a variable and use it to set expression properties for your Execute Process Task to use the returned value.
In the above code you need to add a reference to System.Text.RegularExpressions namespace before using the Regex.Match function. Uri.EscapeDataString comes under System.Data namespace which will be included by default inside the script task.
In the above case function will replace character + with its encoded value %2B which will work well with the WinSCP.
Conclusion
As discussed above the special characters in credentials have to be encoded before generating the URL for FTP in case of applications like WinSCP.
The above illustration gives an easier way of doing it within the SSIS package using a simple .NET function which can be invoked from within a simple script task.
Hope this helps to solve similar issues faced due to the presence of special characters in URLs and in query string parameter values.
The above illustration gives an easier way of doing it within the SSIS package using a simple .NET function which can be invoked from within a simple script task.
Hope this helps to solve similar issues faced due to the presence of special characters in URLs and in query string parameter values.
No comments:
Post a Comment