Book List: Task 2

The next task in this project was to import the text files into a SQL Server 2008 DB.

To do this, I created an SSIS package. I will cover the package creation in another post.

The next step actually bears the point of this series, as attempting the validation of the title/authors has become the crux of the whole thing (as data validation usually is).

The basic problem looks like this:

Column_1 Column_2
Mark Twain Huck Finn
Tom Sawyer Mark Twain
Call Of The Wild Jack London
Arthur Conan Doyle Sherlock Holmes

In the case of Mark Twain and Tom Sawyer, they can both be valid author names - there's no special characters, etc.

So, I went looking for a validation method. My first thought was to use Amazon. That should be pretty simple, right? Just grab an API, plug it in, and away we go!

As it turns out? Not so simple to use Amazon from SSIS.

Book List: Task 1

Getting the book list itself was pretty simple, really, even though the physical files were spread over several devices and/or formats.

In order to make my life easier, I decided that a ANSI/ASCII text file would present the fewest problems. The easy way to do this, of course, seemed to be to go into each device/format and email the files to one PC, and then "Save As..." to get them all to the same physical disk.

It was, suprisingly, just that simple.

Book List

I have a list of books that I want to import into a DB, so I can sort them, group them, and just generally use them as sample data. This doesn't seem like it should be that big of a deal.

Right?

Okay, well, perhaps the list is a little unscrubbed because I both pulled some entries from the Internets and hand entered some others into this list. Still, it shouldn't be that big of a deal, right?

Ha! You'd like to think so, wouldn't you?

So, the simple version of the project plan looks like this:

1. Take multiple files, filled with some combination of

• Author
• Title
• Other Spacing / Whitespace

1. Import said files into a SQL 2008 DB
2. Validate that authors are really authors, titles are really titles
3. Run a single select to indicate the distinct list of books in the set
4. BONUS: Add a column indicating ownership status
5. BONUS: Add a column indicating "have read" status

Blackberry date issues

I have a problem - my BlackBerry, when I sync it, doesn't keep all day events as all day. Instead, it changes them internally to midnight-based.

This is an issue, as once the BB changes them to midnight-based, and they get synced back to Outlook/Exchange, they show up in Outlook as existing only at 12:00AM on the day of the event. Since the bulk of my calendar time isn't spent looking around the overnight hours, but instead is focused on waking hours activities, so it's rather easy to miss activities.

Armed with Outlook macros, I wanted to try and see what was going on under the covers.

I've written a form and a macro to expose all events that have an Outlook start date of midnight.

I ran into a couple of things really quick:

• Outlook stores its dates in UTC
• My time zone is -5
• I have no record of what an event USED to be

Outlook storing dates
Outlook stores its dates in UTC, Universally Troublesome Conversions. Basically, it's a fancy name for not-your-local-time. Yes, I know what it is really.
That said, you're going to have to get the appointment in UTC, and convert it back to your local time zone. Or, if you're a programmer by nature, you do the math in your head, and hardcode the value in during the R&D phase. That said, instead of looking for events at midnight, I'm now looking for them at 04:00.

My time zone
My time zone is -5. I didn't know, when I began, that Outlook was storing things in UTC, so I hadn't recalled what my time zone UTC differential was.

No record
This is actually what's got me stuck. I have no way of knowing, just from programmatically reviewing an events current properties, if it should be an all day event or not. If I did, then I could write a sync program pretty easily. Since I don't, what we're talking about is writing a sync program that would run to "backup" the current Outlook calendar dates, and adjust them before or after the BlackBerry sync runs.

That seems like overkill - after, don't I already have a sync program?

Documenting Requirements

While building a requirements document recently, I came across the challenge of building for purpose. I found that in order be as clear as possible for the developer who was the target audience, I was going to have to build the document in such a way that it didn't know anything about future maintenance.

This presented an interesting dilemma, as how could I then use these same requirements for the maintenance work that I know is coming for this work product?

I found there were certain aspects I could boil it down to, and instead of creating a large, formal document, I boiled the whole thing down to an email with three parts.

• Background - I used this to cover the ground with a general layout of where we were.
• Current Issue - I used this section to cover how we got here, from there, and what exactly "there" was.
• Proposed Solution - Lastly, I outlined what I was proposing we do about it.