Migrate SQLPrompt Snippets to VSCode

-
 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr
Post a Comment

Beware the Errors of SSIS

-
SSIS is a bit of a touchy subject where I work, and the more I use it, the more I sort of understand why.



While SQL Server is my background and I consider myself an expert in TSQL (not like, MVP level or anything), I don't do too much in SSIS. We have a home grown architecture which, in many ways, mimics functionality SSIS can do, and, for our purposes, usually works better and comes with fewer headaches. That said, there are some operations SSIS is simply hands down the best tool for the job. But I didn't come here to talk to you to day about when to or not to use SSIS. No dear reader, I come with a warning/complaint about how it surfaces error.

The description of how it surfaces errors is "not very well". It's undoubtedly something you get better at teasing out over time, but I think largely because of the breadth of systems it needs to interact with, surfacing meaningful errors for specific stages can often seem to have absolutely nothing to do with the error at hand.

Take for instance the following error I got and subsequently spent a day and a half trying to figure out.

[Execute SQL Task] Error: Executing the query "exec alerts.dbo.SyncAddressesBatch @BatchId = ?" failed with the following error: " ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly

Here's the setup. Execute SQL Task, which calls a proc with a single parameter.



What the proc does isn't important. Sufhttps://msdn.microsoft.com/en-us/library/ms140355.aspxice it to say it accepts a @BatchId parameter and then does stuff. 

So how would you go about troubleshooting this issue? Well first, let's look at the error text. The first troubling thing is all the "or"s in the error. Could be A... or B... or C... or something. Googling the error you find responses related to pretty much all the cases there; none of which applied to my situation. There's also the empty string error message.

So with the internet not being able to help, what would you look at? first, you need a little background on Execute SQL Task Parameterization (a topic I'll let you read up on your own if you're interested). The syntax for parameters is a little funky, but basically you put question marks in your SQL where you want to substitute a value, and then on the Parameter Mapping page, you supply variables and name them by the ordinal position the question mark appears (so the first question mark in the script is Parameter Name: 0, the second question mark is Parameter Name: 1, etc). Everything looked on the up and up. I put in a script stage to verify that the parameter I was passing indeed had a value, and wasn't invalid or null or something. Finally I threw up my hands in frustration and went home for the day. 

When I came back I just started fiddling with things. I tried omitting the (required) @BatchId parameter. It correctly surfaced an error from SQL Server saying I didn't provide a required parameter. I tried replacing the @BatchId with a hard coded "1" (getting rid of any parameterization altogether), but still got the same error.

Finally, I think by accident, I put a return statement early on in the proc, and without knowing that, re-ran the package. To my confused delight, the package succeeded. Through a little more sleuthing, I figure out that what was happening was the underlying procedure was hitting a primary key error, and the empty string it was surfacing just happened to be the result of a null concatenation. Easy fix on both counts, but very frustrating to have to arrive at that conclusion in such a circuitous and laborious process

tl;dr, it's difficult to know when to take SSIS's errors seriously and when to assume it's a different component. Other than making these mistakes yourself and learning from them, there's not any deterministic way I can think of to decide which to do when. All I can say is take any errors you see with a healthy dose of skepticism and if you find yourself banging your head against a wall trying to figure out what a given, vague, error really means, take a step back, gather your thoughts, and perhaps approach it from a completely different angle. We often rely on informative error messages to guide our development (and rightfully so). But SSIS has a habit of trolling us developers, whether it knows it or not. Please, do not feed the trolls.

Comments

Post a Comment

Popular posts from this blog

Migrate SQLPrompt Snippets to VSCode

-
 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr
Read more

Fixing Git "Unexpected Disconnect while reading sideband packet"

-
 I ran into an issue the other day (which my co-worker just ran into as well) when doing a git push, where it fails, saying "RPC failed; HTTP 500 curl 22 The Requested URL returned error: 500 send-pack: unexpected disconnect while reading sideband packet". I found almost no useful articles on the matter; most just talked about adding tracing flags or saying "your internet sucks", neither of which addressed the issue. What fixed it for me was setting the http.postBuffer size to something very large, like git config --global http.postBuffer 157286400 This allowed larger files/repositories or something like that to be posted successfully.
Read more

Left-Padding Zeroes

-
 I recently ran across a case where a time value was stored as an Int, with the idea that the first two numbers are the hours, second two the minutes, and third two the seconds. This is all well and good if the hours are double digits (e.g. 144415), as you can just split the string every two characters. If the hours are single digit (e.g. 93251) doing that won't give you the right result. You'll wind up splitting out "93", "25", 1" when what you really want is "09", "32", "51". You also run into this stuff sometimes when you're trying to left-pad a string for a year, month, or date to a pretty format. None of these cases are particularly compelling, and there are probably other solutions for these sorts of issues, but what I DO want to share with you, is a couple ways you can left-pad a string with characters.  FWIW, I've tried doing left-padding with the FORMAT function, and maybe I just suck with it, but I haven&#
Read more