Book List: Task 2.5 - SSIS

So, let's review:

You put in what essentially amounts to userid/password, along with some keywords, and out comes XML. SSIS's webservice component should be able to handle this no issue, right?

Turns out, it should be. Instead, what you actually get is a known issue with the error "the selected web method contains unsupported arguments"

Check out this URL to see what I mean:

http://www.bigresource.com/Tracker/Track-ms_sql-hskONpQF/

So, this bummed me out. I thought "There HAS to be a way to do this!"

In searching for an answer, I also came across the MSN WSDL file, to feed into the SSIS webservice task. This was useful to prove that it wasn't just Amazon's file. You can find it here:

http://soap.search.msn.com/webservices.asmx?wsdl

Book List: Task 2.5 - Introduction

Here's what I want to do:

• Take a string that I suspect is the title of a book, like "Huck Finn".
• Send this title to Amazon's Web Service, the Product Advertising API, to see if it's valid.
• Receive a response indicating the validity of this string as a title using number of matches to validate.

First, I want to do this manually. That, in and of itself, is not quick or easy. First, you have to sign up for an amazon account. That was simple, I already had one. My account was also already an associate, so that helped - I had poked around their site a bit already.

Then, you have to go to

https://aws.amazon.com

and get your amazon.com account registered for Product API access.

That done, you then go back to

https://aws-portal.amazon.com

Where you can now access your security credentials, under the heading of "Access Keys". From here, you grab your two string-based access ID's, and save them off someplace. You'll need them again in a minute.

Now, head to http://associates-amazon.s3.amazonaws.com/signed-requests/helper/index.html

This is a web page that will allow you submit requests manually. It's a syntax checker! Woohoo! A button I can push! :)

Now, I put in the information, and it spits back the various information about the URL. This is cool! Copy and paste the URL from the bottom-most text box labelled "Signed URL" into a spare browser window, and POOF! You have a fully formed amazon result right there! Success!

Most of this information is covered in one form or another in the official "Getting Started Guide":

http://s3.amazonaws.com/awsdocs/Associates/2010-06-01/prod-adv-api-gsg-2010-06-01.pdf

So, let's review:

You put in what essentially amounts to userid/password, along with some keywords, and out comes XML. SSIS's webservice component should be able to handle this no issue, right?

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.