Query not working in execute SQL task in the ssis package

Question

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

What is the issue here?

You'd probably get more help if you posted the error that is thrown. Also, maybe check to make sure you are connecting to the same database/database server with both SSIS and SSMS when you are doing your comparison. — Mr Moose

billinkc billinkc · Accepted Answer · 2012-02-06T15:04:41

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

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.

enter image description here

Query aka SQLStatement

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

Full query used below

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

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

variables, locals and results

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). enter image description here

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.

Query not working in execute SQL task in the ssis package

2 Answers

Query aka SQLStatement