Friday, March 23, 2012

Moving On

Well, it's become time to move on to another day-time home. Pay to Play is something of a dirty word in most parts of the USA (excepting here in NJ, where it's the acceptable alternative to Menace to Meet (the menacing usually demonstrating that one is carrying concealed, licenced or not). I'm kind of unusual, it seems, because I really like doing what people around me all call "work". For me, "work" is "play I get paid for", so Pay to (let me) Play is definitely on my approval list!

As I started to say, I'll be finished up here in mid-north NJ at the end of the month, and starting a new gig near Philly - lots nearer home. It'll actually be a lot more miles per week, but I'll save the cost of a hotel room 5 nights a week. Quids in overall, so that's good. OTOH, I'll be sad to leave, as I've been enjoying the atmosphere and working environment here. 

Today was a really nice day up until about 4 pm. I got lots achieved in the morning, had a really nice lunch, got back to find that the explanation of some really weird specs had arrived, so I sat down and finished that requirement in about 90 mins, testing and all, and set about fixing the code for a report that's displaying some really really weird behaviour (although as I think of it right now I suspect I've just realised why!).

Then I got a request from the friendly local DBA who was deploying an SSIS package of mine. "Come on over, we've got a small problem"  ...  the coded equivalent of "I have a cut-throat razor looking for some skin. Feel like volunteering?" !

So I wandered over to find him really perplexed. "did you encrypt anything here?". "No. Why?" and he shows me some error messages. Stop and re-start it and it all goes away - don't ask; it's MS SQL Server 2008! Anyhow, we test the package and it does all the difficult bits fine and then crashes writing to an Excel file.

It turns out that SSIS dynamic variables are evaluated every time they're accessed, so if I have a variable that is composed of a file path and name and a time-stamp and the extension, and the time stamp is created in a formula using getdate(), then getdate() is triggered and the result used to create the string from scratch every time it's asked for.

That would be fine, but I have two tasks: one copies a template file (an empty Excel file) to another folder, adding the time-stamp en route, and the next one pulls data from a database table and writes it into that file.

All had always worked fine in testing, but today the network was so slow and the data quantity sufficiently large, etc., etc., that the first task composed a new name Test_20120322_1602.xls, but by the time the next task was looking to write to it, the name it composed (in exactly the same way) was Test_20120322_1603.xls. Needless to say, this file didn't exist, so I got a nasty OLEDB Failed message for my troubles!

The fix was to compose the name once first and store it (with a script task) into a non-dynamic file name for both tasks to use.

So here I am at my desk in the hotel, writing this (the first entry for several months - I've just not had a great amount of time  and living in a hotel room doesn't exactly encourage the muse) as, I suppose, a way or procrastinating about doing some studying.

I've been answering a lot of questions on ITToolbox recently, and have re-started on Experts Exchange. In fact, in just a few days of trying I've already been given a free month's use! Answering the different types of questions is always a challenge, and sharpens ones' own skills, which is good.

That's All, Folks!

TTFN