Console heck.

For the record, I hate writing console applications. There’s just only so much magic you can pull without a UI to interact with. But, sometimes, there’s no alternative. Take this problem set:

Basically this is a software key provisioning process, where a central server acts as a key repository – holding a pool of allocated keys. There could be 20 or more provisioning stations that need to pull down these keys, one at a time, and assign it to a serial number – add a hash code, and pass it back to the central database for more processing. Above, we have a “user” running this console application – but since it will be hands-free, writing this as an ETL package (on the server side of things, to handle license requests and keep track of assigned serial numbers) for the server side, and pairing it up with a simple console application that can get kicked off command-line on the individual stations made sense.

The actual implementation console-side was relatively easy – so much so I won’t put it here. The console app calls a sproc to receive a datatable and passes it out as an XML file; it’s then decorated with some attributes (updating the process state and adding a hash), and then it’s picked up again and the record is updated back to the database. Pretty beaten down ground there.

The codeset and SQL is here (except the SSIS) – FFKI

However, the Output SSIS package was funky. Following the steps in this post, I added a data flow to output to an ADO.NET recordset, and then added a ForEach loop against that source.

I originally was going to do a database call within the for/each loop – but that seemed wasteful. I mean, the data is RIGHT THERE. Why are we calling the database twice – once accessing the records in table form to form a recordset, and a second time to form XML and output to a file? So, shudder, I used a cursor.

Tough to say this, but the cursor is actually very performant and works fine as the source for a ADO.NET recordset. This can then be set in a variable called @SerialNumber and @OutputXML in a ForEach loop – and that’s what our script works against. Plain and simple, it accepts it – and writes it out to a file. Easy peasy. We could have updated it as a set but I didn’t want dirty records (in case some were inserted or updated to 1 mid-process) – so that’s done as part of the loop individually. (Another solution is to have yet another ToUpdate status of 2 being mid-process and 3 being done.)

Little things like setting the opening command line arguments I’d forgotten (even how to generate it using csc CMD-side. How quaint!)

SSIS Package Joy

The SSIS package code. Very spaghetti but it works; I don’t think the structure of VB in SSIS encourages modular programming. (Yes it’s a bit of a copout. I found the same thing true with the console app. If there’s no reuse potential and one Main() entry point, what’s the sense in splitting it out into umpteen methods and separate classes?)

Basic order of events:

  1. Reads XML from FilePath + FileName using StreamReader
  2. Adds <Keys> root using FileContent.Replace
  3. Sets up an archive and process folder using ArchivePath (or ProcessPath) + FileName. We could have used FilePath but it’s possible they’ll want separate file drops in the future.
  4. Writes to ProcessPath XML file using StreamWriter, and moves orig file to archive destination.


public
void Main()

{


String ErrInfo = “”;


String FilePath = Dts.Variables[“User::FilePath”].Value.ToString()+ Dts.Variables[“User::FileName”].Value.ToString();


//MessageBox.Show(“Filename: ” + FilePath);


try

{


String FileContent; //Variable to store File Contents

FileContent = ReadFile(FilePath, ErrInfo);


if (ErrInfo.Length > 0)

{

Dts.Log(“Error while reading File “ + FilePath, 0, null);

Dts.Log(ErrInfo, 0, null);

Dts.TaskResult = (int)ScriptResults.Failure;


return;

}


//FileContent Before Replace;


//MessageBox.Show(FileContent);


//Find and Replace –> Modify WHERE clause

FileContent = FileContent.Replace(


“<Key>”,


“<Keys><Key>”

);

FileContent = FileContent.Replace(


“</Key>”,


“</Key></Keys>”

);


//FileContent After Replace;


//MessageBox.Show(FileContent);

Dts.Variables[“User::FileContent”].Value = FileContent;


String ArchivePath = Dts.Variables[“User::ArchivePath”].Value.ToString() + Dts.Variables[“User::FileName”].Value.ToString();// +@”\Processed\” + Dts.Variables[“User::FileName”].Value.ToString();


String ProcessPath = Dts.Variables[“User::ProcessPath”].Value.ToString() + Dts.Variables[“User::FileName”].Value.ToString();


//MessageBox.Show(ArchivePath);


//Write the contents back to File


if (File.Exists(ProcessPath))

{


File.Delete(ProcessPath);

}

WriteToFile(ProcessPath, FileContent, ErrInfo);


if (ErrInfo.Length > 0)

{

Dts.Log(“Error while writing File “ + ProcessPath, 0, null);

Dts.Log(ErrInfo, 0, null);

Dts.TaskResult = (int)ScriptResults.Failure;


return;

}


//and move the orig files


if (File.Exists(ArchivePath))

{


File.Delete(ArchivePath);

}


File.Move(FilePath, ArchivePath);

}


catch (Exception e)

{

Dts.Log(e.Message, 0, null);

Dts.TaskResult = (int)ScriptResults.Failure;

}

}


public
String ReadFile(String FilePath, String ErrInfo)

{


String strContents;


StreamReader sReader;


try

{

sReader = File.OpenText(FilePath);

strContents = sReader.ReadToEnd();

sReader.Close();


return strContents;

}


catch (Exception e)

{


MessageBox.Show(ErrInfo);

ErrInfo = e.Message;


return
“”;

}

}


public
void WriteToFile(String FilePath, String strContents, String ErrInfo)

{


StreamWriter sWriter;


try

{

sWriter = new
StreamWriter(FilePath);

sWriter.Write(strContents);

sWriter.Close();

}


catch (Exception e)

{


MessageBox.Show(ErrInfo);

ErrInfo = e.Message;

}

}

Links of Note

I think, at this point, since I can’t read values at run-time from app.config – and I am out of time and need to move on – that I’ll add them as global variables, however hokey that is, directly in the class.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s