September 6, 2011 at 2:48 pm
I've inherited someone elses SSIS package that contains a fairly complex variable that represents a SQL query. This dynamic sql query references four other variables.
I have just a couple of questions that I hope experienced SSIS developers can answer for me (please).
First, below are the four variables that are referenced in the SQL Command variable:
@[User::_ClientID] (int)
@[User::_LoadAssignments] (Boolean)
@[User::_Scope] (string)
@[User::_Facility] (string)
Second, below is the expression in the SQL Command variable:
"SELECT
P.PatientID
FROM
dbo.PATIENTS AS P
LEFT OUTER JOIN dbo.PROVIDERS AS Prov
ON P.PCP_ID = Prov.PROVIDER_ID
AND P.ClientID = Prov.ClientID
WHERE
P.ClientID = " + (DT_WSTR, 10)@[User::_ClientID]
+ (@[User::_LoadAssignments] ? "
AND Prov.Scope LIKE '" + @[User::_Scope] + "'
AND Prov.Facility LIKE '" + @[User::_Facility] + "'" : "") + "
ORDER BY
P.PatientID;"
Here are my questions:
1) What does the question mark after "@[User::_LoadAssignments] ?" mean? -- Normally, I interpret a question mark to indicate a passed parameter value, but no parameter is passed to this query, just the four variables.
Given that [User::_LoadAssignments] is boolean, I'm wondering if the question mark is merely evaluating if the variable is true/false--> if true, then include the additional criteria of Prov.Scope and Prov.Facility; if false, then include nothing. Is this a correct interpretation?
2) All four variables are defined in a config file, but not all of the variables have values. So, when a variable hasn't been assigned a value, what impact does that have on the above query? (This query can return records when only [User::_ClientID] has been assigned a value, and the other variables are not assigned values.) Does an "empty" variable null out the criteria that is contained within the parentheses? For example, if [User::_LoadAssignments] = true (1?), but [User::_Scope] in the config file has no configured value, what happens?
Thanks in advance!
--Pete
September 6, 2011 at 3:19 pm
Given that [User::_LoadAssignments] is boolean, I'm wondering if the question mark is merely evaluating if the variable is true/false--> if true, then include the additional criteria of Prov.Scope and Prov.Facility; if false, then include nothing. Is this a correct interpretation?
You are correct. Follow this link for an explanation why.
2) All four variables are defined in a config file, but not all of the variables have values. So, when a variable hasn't been assigned a value, what impact does that have on the above query? (This query can return records when only [User::_ClientID] has been assigned a value, and the other variables are not assigned values.) Does an "empty" variable null out the criteria that is contained within the parentheses? For example, if [User::_LoadAssignments] = true (1?), but [User::_Scope] in the config file has no configured value, what happens?
Even though the values are not being set in the config file, they might be set by one of the tasks in the package (probably a script task or an Execute SQL task), so you should check for that.
Use breakpoints to examine the contents of variables as the package runs. Multiple breakpoints will allow you to step through the package and watch the variables' values on the way.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 6, 2011 at 3:23 pm
Phil -- thanks for the quick reply!
Looks like I need to learn about setting breakpoints and viewing the values of variables....
--Pete
September 6, 2011 at 3:57 pm
peterzeke (9/6/2011)
Phil -- thanks for the quick reply!Looks like I need to learn about setting breakpoints and viewing the values of variables....
--Pete
That will take approximately 1 minute 30 seconds!
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 7, 2011 at 8:48 am
To expand a bit on point 2, SSIS variables don't have a "null" value. They always have to have an actual value. If your package configuration doesn't specify one, then the value that it uses could be undetermined, but in the end it will always have an actual value.
For a string data type, the value might be a blank string, for a Boolean it might be FALSE, etc ... but it won't be NULL.
September 7, 2011 at 11:27 am
kramaswamy (9/7/2011)
To expand a bit on point 2, SSIS variables don't have a "null" value. They always have to have an actual value. If your package configuration doesn't specify one, then the value that it uses could be undetermined, but in the end it will always have an actual value.For a string data type, the value might be a blank string, for a Boolean it might be FALSE, etc ... but it won't be NULL.
kramaswamy - Thanks for the additional follow-up. Your comments affirm my assumptions.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply