CODEWITHSUNDEEP
sqlssis
Neo
Neo

Reputation: 16219

how to use variable value inside another variable in SSIS?

I have 3 variables like following in SSIS package

NAME       TYPE    VALUE   
FROMDATE   String  '5/1/2011'     
TODATE     String  Select (FunctionPreviousBusinessDay(),112)
OUTPUT     String  Select companyName , price from Mytable where date in between '+ @[User::FROMDATE] + "and"  + @[User::TODate]'

OUTPUT is giving me evaluated expression like :

Select companyName , price from Mytable where date in between '5/1/2011' and
Select (FunctionPreviousBusinessDay(),112)

Instead of execution of variable TODATE directly giving string.

I'm expecting output like following

Select companyName , price from Mytable where date in between '5/1/2011' and
'5/22/2011'

How can i do this ? please advice ?

Directly paste sql query with ? but getting following error : 

Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Syntax error, permission violation, or other nonspecific error".

Upvotes: 0

Views: 3468

Answers (1)

mr.Reband
mr.Reband

Reputation: 2430

I think you need an additional Execute SQL task with a single row result set. The task's query would be 

 Select (FunctionPreviousBusinessDay(),112)

And its result would need to be set to @TODATE.

Upvotes: 1

Related Questions