how to use variable value inside another variable in SSIS?

Question

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".

This is a follow-on to OP's question stackoverflow.com/questions/16691747/… where the main issue is the use of the SSIS Expression Editor. Also, an OLE DB data source parametrized SQL statement would work better here. — criticalfix
– criticalfix, Commented May 22, 2013 at 14:37
Directly paste inside oledb source editor getting error (updated in question) :( — Neo
– Neo, Commented May 22, 2013 at 14:42

mr.Reband · Accepted Answer · 2013-05-22 14:50:00Z

1

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.

answered May 22, 2013 at 14:50

mr.Reband

2,4312 gold badges20 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to use variable value inside another variable in SSIS?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related