08 January, 2013

Copy Files From an FTP Server Pt. 1

Recently I was posed with a dilemma. We are using a 3rd party application for taking data from a file, scrubbing, quality checking and finally pushing it into a database for use by our main product. This data comes from different sources and is dropped onto an FTP server. The 3rd party application could technically hand that as it can function “on file drop”. There are, however, two limitations to the 3rd party app’s functionality.

  • It does not recognize that a file has changed. Only that a new file has arrived.
  • The 3rd party app runs on a server that is on a different network than the FTP server is on.

With these issues in mind, my task was to make it work, and make it work on file drop.

The solution I arrived at was to create a console application that will poll the FTP server, and if a file drop has occurred, copy that file to a location where the 3rd party service can see it. I should note that there are some 500 clients each with their own file drop. Some clients are migrated to using the new 3rd party application while others still are on the old, and rather inefficient way of importing this data.

First, I need some static properties. Note the RE_GEX constant, this will allow me to parse the string returned from the FTP server directly listing call. I got this expression from a Bing search, not sure where I got it from, but it does work.

static string RootFtpUri;
static string FtpDestinationRoot;
const string RE_GEX = @"^([d-])([rwxt-]{3}){3}\s+\d{1,}\s+.*?(\d{1,})\s+(\w+\s+\d{1,2}\s+(?:\d{4})?)(\d{1,2}:\d{2})?\s+(.+?)\s?$";
static Regex _regex;
static NetworkCredential Credentials;
static string LogFilePath;

I setup the properties:

FtpDestinationRoot = ConfigurationManager.AppSettings["FtpDestinationRoot"];
var di = new DirectoryInfo(FtpDestinationRoot);
if (!di.Exists) di.Create();
RootFtpUri = ConfigurationManager.AppSettings["RootFtpUri"];
Credentials = new NetworkCredential(ConfigurationManager.AppSettings["FtpUsername"], ConfigurationManager.AppSettings["FtpPass"]);
_regex = new Regex(RE_GEX, RegexOptions.Compiled | RegexOptions.Multiline | RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace);

Of course, I use the AppSettings from the app.config file to hold all the pertinent information just in case something changes, like the FPT server name, or the login credentials required. This allows me to make those changes without having to re-build and re-deploy the application.


The data for each dealer, their FTP username, drop directory and the name of the dropped file is stored in a MSSQL database. My application then, polls the database to see which files I would need to copy over to the new location.  I have a Records class which holds the required data from the SQL database. I put that into a list.


var records = (from d in dt.AsEnumerable()
select new FTPRecord
FTPServerAddress = d.Field<string>("FTPServerAddress"),
FTPServerUsername = d.Field<string>("FTPServerUsername"),
FTPServerPassword = d.Field<string>("FTPServerPassword"),
FTPServerFilePath = ParseFPTServerFilePath(d.Field<string>("FTPServerFilePath")),
DMSTypeID = d.Field<Int16>("DMSTypeID"),
Status = d.Field<string>("Status"),
RecordType = d.Field<string>("RecordType"),
NextScheduledAttempt = d.Field<DateTime?>("NextScheduledAttempt")

The call to the method “ParseFTPServerFilePath” merely strips away any preceding slashes that sometimes get put in when the data is created.

Then it goes to the FTP server and looks for the file, if it exists, I copy the file over. But wait, remember how the 3rd party app only sees new files and not changed files? That process doesn’t work, so I first delete the existing file in the new location before copying the new file from the FTP server.

We have to setup the required stuff for accessing the FTP server, as well as where we are going to put the file. If the destination directory does not exist, we of course have to create it or the whole thing will blow up – and not in a spectacular way so it’s really just a waste of time and effort not to do it.

var destinationFilePath = string.Format("{0}\\{1}\\{2}", FtpDestinationRoot, r.FTPServerUsername, r.FTPServerFilePath.Replace('/', '\\'));
var fi = new FileInfo(destinationFilePath);
var di = new DirectoryInfo(fi.DirectoryName);
if (!di.Exists) di.Create();
var ftpFilePath = string.Format("{0}/{1}/{2}", RootFtpUri, r.FTPServerUsername, r.FTPServerFilePath);
var request = (FtpWebRequest)WebRequest.Create(ftpFilePath);
request.Credentials = Credentials;
request.Method = WebRequestMethods.Ftp.ListDirectoryDetails;
var line = string.Empty;

Then connect and grab the directory listing from the FTP server. This comes back in the standard UNIX format which looks something like:

-rw-r--r-- 1 ftp ftp        2107325 Jan 08 00:24 INV.CSV

The RegEx takes each line and chunks it into groups, I check to see if the first group is a “d” – this signifies it is a directory – which I bypass and go to the next line. I also check to see if the 6th group is a dot or double dot (“.” or “..”) these are not real files so I pass that line up as well. Now, if the first group is not “d” and the 6th group is not “.” or “..” that means I have a file! W00t!

The RegEx spits out groups in the following format:

comp.Groups[0]    {-rw-r--r-- 1 ftp ftp        2107325 Jan 08 00:24 INV.CSV}
comp.Groups[1] {-}
comp.Groups[2] {r--}
comp.Groups[3] {2107325}
comp.Groups[4] {Jan 08 }
comp.Groups[5] {00:24}
comp.Groups[6] {INV.CSV}

Group 0 is the actual directory entry string, group 3 is the size, 4 & 5 are the date and time and 6 is the file name.

using (var response = (FtpWebResponse)request.GetResponse())
using (var responseStream = response.GetResponseStream())
using (var sr = new StreamReader(responseStream))
while ((line = sr.ReadLine()) != null)
var comp = _regex.Match(line);
if (comp.Groups[1].ToString() == "d") continue;
if (comp.Groups[6].ToString() == "." || comp.Groups[6].ToString() == "..") continue;
if (fi.Exists)
var ftpSize = NullableParser.ParseInt64(comp.Groups[3].ToString());
var ftpLastDate = NullableParser.ParseDateTime(comp.Groups[4].ToString());
if (ftpLastDate > DateTime.Now) return; // File is actually from last year (or older) but FTP only returns month/day and .NET assumes current year.
if (r.NextScheduledAttempt == null || ftpSize != fi.Length || ftpLastDate > fi.LastWriteTime)
fi = new FileInfo(destinationFilePath);


Then comes another problem, what if the file on the FTP server hasn’t changed? I don’t want to unnecessarily copy files that haven’t changed and cause the import process to kick off if it isn’t needed. So I check first for the size of the file, if the size is different, I delete the existing and copy the new. If the file is the same, I then check the date on the file, if the date of the file on the FTP server is newer than the one on the network location, I delete and copy.

If there is no file on the destination location, I copy it from FTP without further ado. This affords us a manner in which we can re-import the data if need be for whatever reason. We simply go to the network location and delete the file and it will be re-copied and kick off the import process.

Next time, I’ll go through creating the Windows Service that runs this thing.

19 May, 2011

Recovering a SQLEXPRESS Installation

I inherited a desktop machine at work. It wasn’t scrubbed and re-fitted with a fresh install when I arrived. Recently I have been examining processes for Single-Sign-On, and really needed to play around with the local installation of SqlExpress, so as not to screw up the dev database for everyone else. One problem though, the previous tenant of the machine followed best security practices and remove the BUILTIN\Administrators user from the database and I nobody knew the System Administrator (sa) password - add to that, the sa account was disabled (again, good security practice!).

With that in mind, I set out on the path to discover how I can recover that installation, and not have to go through the process of uninstalling and re-installing SqlExpress. I finally came across this old post at Phoenix blog.

A few things to keep in mind; His instructions are for full-blown SQL Server, so instead of using “NET STOP MSSQLSERVER” you’ll have to use “NET STOP MSSQL$SQLEXPRESS”. This is for all the stop and starts. Also, one has to have local administrator privileges on the machine, if you don’t have that, as far as I know, you’re pretty much up a creek without a paddle. The process is quick and simple really, I’ll post the steps here (without the nice screen-scrapes that Deepak has in his blog, you can go there and look at them if you need to).
1) Open command line window
2) type NET STOP MSSQL$SQLEXPRESS and wait for the service to stop
3) type NET START MSSQL$SQLEXPRESS /m - this puts sql server in single-user mode (I am not sure what that is really, but hey,it works!)
4) Open your SQL Server Management Studio, and click “New Query” you’ll be presented with the connection dialog window, select the instance of SqlExpress and open the connection.
5) In the new query window type “sp_addsrvrolemember ‘YOUR_USER_NAME’, ‘SYSADMIN’” (sans quotes, of course). This will add your login to the sysadmin role.
6) Close Management Studio.
7) Back in the command prompt window type “NET STOP MSSQL$SQLEXPRESS” to stop the service
8) Next time “NET START MSSQL$SQLEXPRESS” in the command prompt to start the service again, but in normal mode (not single user mode this time, notice the lack of ‘/m’ switch in the command.

06 May, 2011

SalesForce excitement with .NET

Been getting to work some with the SalesForce Apex API, grabbing and pushing data from and to the SalesForce data store. After a bit of a learning curve, it's turning out to not be fairly easy going. I do need to look a bit deeper into bulk uploads, as some of the stuff we'll be pushing is 30K plus recordsets, granted, that isn't a lot, but could be enough that using their bulk API might make sense.

Next project on deck is creating a Single-Sign-On into SalesForce. This is going to be a bit more daunting I think. All of the SalesForce samples and help deal with OpenSSO running on it's own server. We're going to go with the Windows Identity Foundation tools, using the SSO into SalesForce as a launching pad for a new login process for all of the apps across the board. Creating an SSO for everything will mean that all logins will hit the same page, utilize the same processes, no matter if they come to the app via a web browser, or an iPhone, or a Xoom tablet or whatever. Should be great fun and a terrific learning experience.

FireFox 4 jQuery document height and width

I ran into a little problem the other day. I had some jQuery goodness running along just fine and dandy. Everything was Jake while using Chrome and Internet Explorer 9 and FireFox 3, but when I looked at the site using FireFox 4, it didn't work right.

After some aggravating trial and error, I discovered the culprit. See, in the jQuery documentation, they say you can use two methods to get the height and width of a document:


The former gives you just the pixel numbers, while the latter is supposed to give you the entire CSS height (i.e. "450" for the first, and "450px" for the second). The problem is, that FireFox 4 doesn't recognize $(document).css('height') - or width for that matter. So you have to get it with just the .height() command, and append 'px' to the end of it.

17 March, 2011

CSS funkiness with Firefox

Normally, I check page layout and styles in IE and in Firefox. These two, I've found, have pretty much been good. If it works in those, it works usually in Chrome and Opera as well. I never check Safari, frankly that browser just slows down my system. My advice to folks using Safari is pretty much "use something else".

Anyway, I was fitting a checkbox today, and cursed a little under my breath when it didn't show up properly positioned in IE9. It's possible that if I'd been using IE8 or even IE7, I might not have found this, since those pretty much handle CSS in the same manner as Firefox. To be honest, I was ready for this to be IE9's fault, so I fired up Chrome and Opera to verify my assumptions. You could have knocked me over with a feather when the page rendered in Chrome and Opera just the same as it did in IE9. It was Firefox that was the problem.

Now, I'm used to making exceptions for IE, so I had to do a bit of research on how to do it with Firefox, and this is what I found:

<style type="text/css">

@-moz-document url-prefix() {

Essentially, I added a second class specifically for Firefox and used those properties only if the browser is Mozilla.

06 June, 2008

ESRI & ASP.NET Master Pages

One of the great things about VS 2005 & ASP.NET 2.0+ is the ability to quickly and easily create and user Master Pages in your ASP.NET web application.

One of the horribly difficult things about Master Pages is when using mash-ups, figuring out how they work exactly. Ok, if you don't know about Master Pages, then you should probably investigate them some.

A Master Page is not really a page, but rather a template that holds containers into which pages render (but can also regular HTML there as well). So a Master Page can have your title, image banner, navigation links, login stuff, breadcrumb trail...all that stuff, and also contains one or more "ContentPlaceHolder" controls.

The problem when putting an ArcGIS Server map in a page that has a master page is that when the page is rendered, the name of the map changes. That is, if you leave you map the default "Map1" name, when rendered, it has the name "ct00_ContentPlaceHolder1_Map1". Assuming, of course, you lave the ContentPlaceHolder name the default AND you put the map in the first instance of the ContentPlaceHolder controls.

This creates issue when attempting to access the map via JavaScript. In the WebMapApp.js file that is defaultly created when a Web Mapping Application is created in VS2005, it is assuming that you leave everything as the default names when you put stuff in the map. With that in mind, it uses names like "Map1", "TaskResults1" etc.

To fix this, I created a global var in the javascript file:

var webMapControlPrefix = "ctl00_ContentPlaceHolder1_";

So, for instance, the setpageElementSizes() function goes to define the scale bar , it used to do:

webMapAppScaleBar = document.getElementById("ScaleBar1");

But now, I have it looking like this:

webMapAppScaleBar = document.getElementById(webMapControlPrefix+"ScaleBar1");

You probably get the idea. If not, drop a comment and I'll see what I can find out about whatever confusion there is.

21 April, 2008

GridView RowUpdating: Stupid, stupid stupid error

Making an error in your code is one thing. But making an error that is so brilliantly, blatantly and egregiously stupid...well, that is something wholly and completely different.

Case in point. I have a GridView control on a page. I need to be able to edit that GridView control. However, to populate the GridView, I need a dataset which contains a " WHERE [COLUMN] IN (values)" where clause. The problem is, that the SqlDataSource really doesn't seem to support this very easily. I'm almost-but-not-quite certain it probably-might-possibly does, but I couldn't figure it out.

Because of that, I needed to bind the GridView to a DataSource programmatically. And that I did, right off the bat, right when the page loads, because, you know, I want the damn data right the hell away.

So what I neglected to do was put the data binding declarations in the good ol' trusty "if (!Page.IsPostBack)" statement. Which means that each and ever time the page loaded (i.e, when I clicked the "Edit" and "Update" links) the GridView was rebound to the original data. No fracking wonder my stuff wasn't working.

31 March, 2008

Highlighting features with ArcGis Server 9.2

I have the need to highlight items when the user selects to zoom to a particular item from a custom search. If I was using the ResultsTask control, then the user could simply check the checkbox next to the feature, and it would highlight. However, since the client doesn't like the treeview style of the ResultsTask, I have put the search results into an HTML table. Now, when the user selects to zoom to a feature, it will automatically highlight the feature.

I found this code in the ESRI forums, and it works well.

// This gives us a nice light blue color. You can change the color to whatever you want.
ESRI.ArcGIS.ADF.ArcGISServer.RgbColor irgbc = new ESRI.ArcGIS.ADF.ArcGISServer.RgbColor();
irgbc.Red = 50;
irgbc.Green = 255;
irgbc.Blue = 255;
irgbc.AlphaValue = 255;

ESRI.ArcGIS.ADF.ArcGISServer.FIDSet fids = new ESRI.ArcGIS.ADF.ArcGISServer.FIDSet();
int[] ids = new int[1];
ids[0] = Convert.ToInt32(key); //<-- OBJECTID of the feature to highlight
fids.FIDArray = ids;

ESRI.ArcGIS.ADF.ArcGISServer.MapDescription mapdesc = ((MapFunctionality)mf).MapDescription;
ESRI.ArcGIS.ADF.ArcGISServer.LayerDescription[] layerdescs = mapdesc.LayerDescriptions;
ESRI.ArcGIS.ADF.ArcGISServer.LayerDescription layerdesc = layerdescs[layerid];
layerdesc.Visible = true;
layerdesc.SelectionColor = irgbc;
layerdesc.SelectionFeatures = fids.FIDArray;

The problem with the above code is that it highlights the entire area. Not bad for point features, but for polygons, it covers everything underneath it completely. I tried adjusting the RgbColor.AlphaValue, but that didn't change anything at all. I'm thinking that is a red herring, and doesn't really do anything at all. So what I decided to do was instead of filling the entire polygon, I just use ShowSelectBuffer as seen below:

ESRI.ArcGIS.ADF.ArcGISServer.RgbColor irgbc = new ESRI.ArcGIS.ADF.ArcGISServer.RgbColor();
irgbc.Red = 50;
irgbc.Green = 255;
irgbc.Blue = 255;
irgbc.AlphaValue = 255;

ESRI.ArcGIS.ADF.ArcGISServer.FIDSet fids = new ESRI.ArcGIS.ADF.ArcGISServer.FIDSet();
int[] ids = new int[1];
ids[0] = Convert.ToInt32(key);
fids.FIDArray = ids;

ESRI.ArcGIS.ADF.ArcGISServer.MapDescription mapdesc = ((MapFunctionality)mf).MapDescription;
ESRI.ArcGIS.ADF.ArcGISServer.LayerDescription[] layerdescs = mapdesc.LayerDescriptions;
ESRI.ArcGIS.ADF.ArcGISServer.LayerDescription layerdesc = layerdescs[layerid];
layerdesc.Visible = true;
layerdesc.ShowSelectionBuffer = true;
layerdesc.SelectionFeatures = fids.FIDArray;

By using LayerDescription.ShowSelectBuffer, the feature is outlined with the highligh color, instead of filled in. This gives us the ability to see what is within the feature, while still knowing where the feature was located.