查询在 ssis 包中的执行 SQL 任务中不起作用 [英] Query not working in execute SQL task in the ssis package

问题描述

此查询在 SQL Server 2005 的查询窗口中运行良好，但在 ssis 包中的 Execute SQL Task 中运行时抛出错误.

This query works fine in the query window of SQL Server 2005, but throws error when I run it in Execute SQL Task in the ssis package.

declare @VarExpiredDays int
Select  @VarExpiredDays= Value1 From dbo.Configuration(nolock) where  Type=11                      
  DECLARE  @VarENDDateTime datetime,@VarStartDateTime datetime                
  SET @VarStartDateTime= GETDATE()- @VarExpiredDays            
  SET @VarENDDateTime=GETDATE(); 

  select  @VarStartDateTime
  select  @VarENDDateTime
  
   SELECT * FROM
(SELECT CONVERT(Varchar(11),@VarStartDateTime,106) AS VarStartDateTime) A,
(SELECT CONVERT(Varchar(11),@VarENDDateTime,106) AS VarENDDateTime) B
  

这里有什么问题?

推荐答案

您的目的是检索开始和结束的值并将它们分配给 SSIS 变量.

Your intention is to retrieve the values of start and end and assign those into SSIS variables.

正如@Diego 上面提到的，这两个 SELECTS 会引起麻烦.对于执行 SQL 任务，您的结果集选项为无、单行、完整结果集和 XML.丢弃 XML 选项是因为我不想处理它，而 None 因为我们想要行返回，我们的选项是 Single 或 Full.我们可以使用 Full，但是我们需要返回相同数据类型的值，然后处理会变得更加复杂.

As @Diego noted above, those two SELECTS are going to cause trouble. With the Execute SQL task, your resultset options are None, Single Row, Full resultset and XML. Discarding the XML option because I don't want to deal with it and None because we want rows back, our options are Single or Full. We could use Full, but then we'd need to return values of the same data type and then the processing gets much more complicated.

通过消除过程，我们使用单行结果集.

By process of elimination, that leads us to using a resultset of Single Row.

我通过简单地删除前面提到的两个 SELECTS 来更正提供的查询.最后的select可以简化为如下(无需放入派生表)

I corrected the supplied query by simply removing the two aforementioned SELECTS. The final select can be simplified to the following (no need to put them into derived tables)

SELECT 
    CONVERT(Varchar(11),@VarStartDateTime,106) AS VarStartDateTime
,   CONVERT(Varchar(11),@VarENDDateTime,106) AS VarENDDateTime

下面使用的完整查询

declare @VarExpiredDays int
-- I HARDCODED THIS
Select  @VarExpiredDays= 10                 
  DECLARE  @VarENDDateTime datetime,@VarStartDateTime datetime                
  SET @VarStartDateTime= GETDATE()- @VarExpiredDays            
  SET @VarENDDateTime=GETDATE(); 

/*
  select  @VarStartDateTime
  select  @VarENDDateTime
*/
   SELECT * FROM
(SELECT CONVERT(Varchar(11),@VarStartDateTime,106) AS VarStartDateTime) A,
(SELECT CONVERT(Varchar(11),@VarENDDateTime,106) AS VarENDDateTime) B

验证执行 SQL 任务是否按预期运行.此时，只需将输出连接到 SSIS 变量即可.正如您在下面的结果窗口中看到的，我创建了两个包级变量 StartDateText 和 EndDateText，类型为 String，默认值为空字符串.您可以在 Locals 窗口中看到它们分配的值对应于提供的源查询中的 @VarExpiredDays = 10

Verify the Execute SQL Task runs as expected. At this point, it simply becomes a matter of wiring up the outputs to SSIS variables. As you can see in the results window below, I created two package level variables StartDateText and EndDateText of type String with default values of an empty string. You can see in the Locals window they have values assigned that correspond to @VarExpiredDays = 10 in the supplied source query

只需配置执行 SQL 任务"的结果集"选项卡即可.最难的部分是确保您在源系统类型和 SSIS 类型之间有正确的映射.对于 OLE DB 连接，结果名称与查询中的列名称无关.这只是按顺序位置(基于 0 的计数)引用列的问题.

Getting there is simply a matter of configuring the Result Set tab of the Execute SQL Task. The hardest part of this is ensuring you have a correct mapping between source system type and SSIS type. With an OLE DB connection, the Result Name has no bearing on what the column is called in the query. It is simply a matter of referencing columns by their ordinal position (0 based counting).

最后的想法是，我发现最好将内容保留在它们的基本类型中，例如日期时间数据类型，并让界面将其格式化为漂亮的本地化值.

Final thought, I find it better to keep things in their base type, like a datetime data type and let the interface format it into a pretty, localized value.

这篇关于查询在 ssis 包中的执行 SQL 任务中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！