August 27, 2020 at 1:41 pm
This one has me flummoxed. SSIS variables are not capable of holding a NULL value, so it seems seriously inconvenient that I can't construct a query with NULL values as well as ?'s for the other parameters, as then the Execute SQL task complains that it can't parse the query. Is that a false error, or will I just die at runtime? Is there a specific way you would have to construct the SQL query statement that I currently have set as:
EXEC myschema.mysproc ?, ?, ?, ?, ?, NULL, NULL
I need to find a way to make this work. I only discovered the problem with trying to use NULL values for variables this morning, and that was a rather disappointing and frustrating find. My previous query was:
EXEC myschema.mysproc ?, ?, ?, ?, ?, ?, ?
As the last two parameters happen to be varchar, they end up as empty strings instead of the desired NULL values... Anyone have a solution?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 27, 2020 at 2:31 pm
This is an unfortunate failing in SSIS, in my opinion. You are correct, in that the variables can't hold NULL values in SSIS. Therefore, unfortunately, you have to handle this in your Procedure instead. For a varchar then you would likely pass a blank string ('') or you could pass the value 'NULL'. Then at the start of your Procedure use NULLIF to change the value to NULL: SET @Param = NULLIF(@Param,'').
For other data types, you would need to pass other nonsensical values for your procedure. If, for example, it's an ID that's always positive you could pass 0 or perhaps -1. For a date 1900-01-01 or perhaps 9999-12-31.
None of these are ideal, I agree, but this is something that I am sure has been asked for in the past and hasn't been addressed; Microsoft's response has always been that you need to change the procedure/SQL not ask them to add functionality (that the application should have).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 27, 2020 at 2:37 pm
Yeah, I expected as much... gee thanks Microsoft! Anyway, I finally decided to try and "outsmart" SSIS and just let the NULL values hang out in the query statement, following the parameter values, and sure enough, it actually worked. So basically, it's a BS error, making the appearance for MS even worse - they should be ashamed of themselves for calling out an error for something that actually works.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 1, 2020 at 2:08 pm
Just an fyi, but this problem was the why behind two other posts, as I had been thinking that the only way to make the Execute SQL Task work with a sproc was to provide ALL parameters via a variable, so I had been trying to set the values of string variables and the info I saw online wasn't quite complete, and I ended up causing myself no end of grief as a result. The final solution to all my troubles can be found here:
https://www.sqlservercentral.com/forums/topic/ssis-expression-task-does-it-even-work
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply