Some days are sent to try us, and the others are just bad days with at least a glimmer of alcohol at 2 minutes past home time, home time is of course currently defined as the moment you move from the chair in the office/kitchen of your own home to any other chair within the building. I had one such of these the other week, the former, not the latter of course. Looking after a 2008 SSIS (I know… I know…. it’s a work in progress, honestly) I was refactoring one series of workflows to connect to a stored procedure which was passing across a variable date value as the ONLY parameter to the stored procedure. Except…. Things were not going well.
The stored procedure itself was called DSCGetSecondaryEventingData and had one parameter called @CaptureDate; this was defined in the stored procedure as a date time because, well… I’m not a monster. Thats what you do. So, as you would do I defined the OleDbsource like this:-
Nothing controversial there, I’ve used this exact layout 1001 times before. But for some reason I could not save this source object… If I pressed on the Parse Query command button I received the following beautifully meaningless error.
I felt like I was back in the 1990’s. … So i tried to preview the data which had just as helpful an error message, at least it would be helpful it if wasn’t blatantly false.
For dramatic effect what followed was many hours of trying new things, comparing old code, swearing, headaches, incredulity, more swearing and further incredulity. It looked a lot like this
Finally I resorted the age old technique of the coding teddy bear, this methodology is where you pick your favourite teddy bear and tell them what you’re trying to do.The act of doing so forces you to
- Break the problem down into simplistic possible salient facts
- Talk about it in ways which your mentalese allows you to just skip.
- Look a complete idiot.
For my teddy bear I chose my colleague Mike.
He’s a bit hairy, not particularly cute but more knowledgable about SSIS than your average care bear….. After a 15 minute furry consultation I made a startling discovery. Tucked away in the connection object is a property called RetainSameConnection, my connection in this project (unlike my other project ) had this value deviating from the default of True. Surely… such a thing could not be the cause of such a fundamental issue? I changed the value to the healthy default of False and ‘Hey Presto’, upon Parsing the query….
True, I could still no longer view the query which I’ll take so long as it saved, compiled and would run…. All of which it did.
So in conclusion, I swore….. a lot…… so that you don’t have to. And seriously, pair programming with a teddy bear? Try it some day.