首页 > 疑难杂症 > SSIS调用存储过程失败

SSIS调用存储过程失败

疑难杂症 ,

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

image

Invalid length parameter passed to the substring or left function.

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

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

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组件可不认你的假设。(再一次证明了,编程的时候假设越少越好)

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

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语句了。

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

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

本博客遵循CC协议2.5,即署名-非商业性使用-相同方式共享
写作很辛苦,转载请注明作者以及原文链接~
如果你喜欢我的文章,你可以订阅我的博客:-D点击订阅我的文章