SSIS调用存储过程失败

最近更新了一个Sql存储过程的实现，结果导致做ETL的SSIS（Sql Server Integration Services）包出现了问题。在调试状态下发现，SSIS在启动时对包做校验的过程中抛出了异常，显示我修改后的这个存储过程有问题。

Invalid length parameter passed to the substring or left function.

奇怪的是，这还不是调用的时候抛出了异常，而是在校验阶段（Validation Stage）就抛出了异常。我感到纳闷，难道SSIS或者Sql Server还能对我的存储过程作单元测试？？

用这句错误信息上Google搜了一下，发现问题是出在CharIndex函数和Substring函数的联合调用上。为了简化，我下面只摘取了一段出问题的代码：

?

1 2 3

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

以前的VBA函数还有Sql函数都喜欢使用1作为数组起始索引，用0表示返回失败，而不像现在C风格下使用0作为起始索引，-1作为失败返回值。charindex这个sql函数是用来查找某个字符串在特定字符串中的位置，如果找不着匹配的，则返回0。而下一行代码中的substring又传入了一个@index-1的参数，因此此参数有可能为负数。猜测就是因为这样，所以SSIS检测失败，抛出异常。之所以当时这么写，是因为基于一个假设，就是业务系统中，此字段都会包含/字符。但是人家SSIS组件可不认你的假设。（再一次证明了，编程的时候假设越少越好）

既然如此，我们就多加一层检测。修改代码如下：

?

1 2 3 4

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

启动SSIS包，结果还是抛出一样的错误。这就奇怪了。难道做了检测还不行？难道substring的第三个参数非得是大于0的？试了一下，改成if(@index > 1000)都不行。

想了半天，最终我只能怀疑，SSIS的这个校验组件，是通过判断变量什么时候被赋值来获得值的可能范围是多少，而不会判断上下文是否有对变量做有效性检测。也就是说，在上面这个存储过程中，@index变量只在charindex函数返回时被赋值，值范围是0~正无穷。因此传给substring的第三个参数的时候，@index – 1的值范围就变成了-1~正无穷了。它直接无视我上面的if语句了。

把这个猜想和同事讨论了一下，他设计了一个验证方案，就是添加一行永远不会被执行的的赋值语句。代码如下：

?

1 2 3 4 5 6 7

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

注意上面代码中将@index赋值为1000的操作，是不会被执行的。可是却通过了SSIS的校验。这说明，SSIS的校验组件做的工作实际上是非常简单的校验而已，不会参考上下文代码。

既然如此，这个校验对于开发人员来说，不但没有意义，而且还会影响我们编写逻辑正确的代码。因为这个校验是不能被关闭的。也就是说，为了绕过这个没意义而且错误的校验，我不得不在charindex和substring这种联合调用的代码中加入一行永远不会被执行的赋值语句来迷惑SSIS的校验组件。否则SSIS的包根本启动不了。

感觉这应该是一个设计上的Bug。

希望对遇到这个问题的人有所帮助。

——Kevin Yang