SSIS failed to call the Stored Procedure

Recently updated the implementation of an SQL stored procedure. As a result, an error occurred in the etl ssis (SQL Server Integration Services) package. In the debugging status, it is found that the SSIS throws an exception during the packet verification process at startup, indicating that there is a problem with the modified storage process.

Invalid length parameter passed to the substring or left function.

It is strange that this does not throw an exception during the call, but throws an exception during the Validation Stage. I am wondering, can SSIS or SQL Server still perform unit tests on my stored procedures ??

Google searched the error message and found that the problem lies in the joint call of the CharIndex function and the Substring function. To simplify the process, I extracted only a piece of problematic code:

declare @index intset @index = charindex('/',@url,1)set @url = substring(@url,1,@index - 1)

In the past, VBA and SQL functions both liked to use 1 as the starting index of the array, and 0 to indicate failure to return. Instead of using 0 as the starting index in the current C style,-1 as the return value of failure. The charindex SQL function is used to find the position of a string in a specific string. If no match is found, 0 is returned. The substring in the next line of code passes in a @ index-1 parameter, so this parameter may be negative. This is why the SSIS detection fails and an exception is thrown. The reason why this was written at that time is that, based on a hypothesis, this field contains/characters in the business system. However, the SSIS component does not recognize your assumptions. (Again, the fewer the assumptions, the better)

In this case, we need to add an additional layer of detection. The modification code is as follows:

declare @index intset @index = charindex('/',@url,1)if(@index > 0)  set @url = substring(@url,1,@index - 1)

When the SSIS package is started, the same error is still thrown. This is strange. Isn't it possible to perform a detection? Must the third parameter of substring be greater than 0? I tried it and changed it to if (@ index> 1000.

After thinking for a long time, I can only doubt that this validation component of SSIS gets the value possible range by judging when the variable is assigned a value, it does not determine whether the context checks the validity of the variable. That is to say, in the above stored procedure, the @ index variable is assigned only when the charindex function returns, and the value range is 0 ~ Positive infinity. Therefore, when the third parameter is passed to the substring, the value range of @ index-1 is-1 ~ It's infinite. It directly ignores the if statement above.

After discussing this conjecture with his colleagues, he designed a verification scheme to add a row of value assignment statements that will never be executed. The Code is as follows:

declare @index intset @index = charindex('/',@url,1)if(@index > 0)begin  if(1=0) set @index = 1000  set @url = substring(@url,1,@index - 1)end

Note that the operation that assigns @ index to 1000 in the above Code will not be executed. However, it has passed the SSIS verification. This shows that the SSIS validation component is actually very simple, and does not refer to the context code.

In this case, this verification is meaningless for developers and affects the coding of logically correct code. This verification cannot be disabled. That is to say, in order to bypass this meaningless and incorrect verification, I have to add a row of value assignment statements that will never be executed in the code of the combined call of charindex and substring to confuse the SSIS validation component. Otherwise, the SSIS package cannot be started.

This should be a design Bug.

I hope it will be helpful to anyone who has encountered this problem.

--Kevin Yang