Every now and again I find myself working with SSIS for one reason or another. Every now and again I find myself fighting with SSIS because it provides less than useful error messages. This is one of those error messages that can be a pain until you understand what can be causing it. Albeit, that doesn’t help resolve the problem with the error message or with its usefulness or the pain that it can/will cause you.
The error message “Value does not Fall Within the Expected Range” is tied to the execute sql task that can be placed in the control flow.
Let’s assume we have such a task in our control flow as demonstrated by the attached image. We’ll call the Execute SQL Task “Parse Client Code.”
Inside of this task, we will probably have something like what is seen in the next image.
Here we have attached parameters to the task and even assigned those parameters to “variable” names that we might use within the query. In this case, we are just trying to parse a code from a filename that can be used in downstream processing. The code might look like the following.
[codesyntax lang="tsql"]
Declare @flatfilename varchar(128) = @FileName
Select left(@flatfilename,charindex('_',@flatfilename)-1)[/codesyntax]
If I run that task at this point, I will get an error. The error could be for any number of reasons based on the setup that I just showed. The most common is that the Parameter Name is not really a name but really should be an ordinal position as to when the parameter is used in the script. The ordinal position is 0 based.
The second issue is the data type that has been selected in error. This should be a name and not a guid. This means I should change the data type to the varchar type from the drop down that is available on the parameter screen under data type.
The next issues is the use of the variable name in the script itself. One should use a ? instead of variable names. So, this script should be fixed to look like the following.
[codesyntax lang="tsql"]
Declare @flatfilename varchar(128) = ?
Select left(@flatfilename,charindex('_',@flatfilename)-1)[/codesyntax]
And the parameter screen should be made to look like the following.
These quick fixes can eliminate or even prevent what might possibly be a headache when dealing with SSIS.
Now, what if you need to have more than one parameter for your query? No problem. The same principles apply. Just map your parameters according to proper data type and to the ordinal position that the parameter needs to be used within the query and you should be all set.
