How do i put where condition using Package variables in SSIS

i have created one ssis package I need run following query

Insert into  mydata.dbo.MonthEndCDSSpreadCalc(Date,CompanyName)
SELECT    Date, CompanyName
FROM       mydata.dbo.UpdateNAV
WHERE     (Date = @[User::Date]) AND (PortfolioId = @[User::PortfolioId]) AND (SecurityType in开发者_开发知识库 (@[User::SecurityType]))

but in above i'm getting error at user variable. running above query into SQL Task please tell me how do i use them properly?

If it should be task inside control flow, use ExecuteSQL task. If it should be task inside data flow, use OleDbCommand.

Also, whichever you pick, you should change your query to use appropriate syntax for parameters. Take a look at BOL for ExecuteSQL task and OleDbCommand for detailed instructions.

For instance, if You are using oledb connection manager in oledbcommand You should use ? only since that is oledb parameter placeholder:

Insert into  mydata.dbo.MonthEndCDSSpreadCalc(Date,CompanyName)
SELECT    Date, CompanyName
FROM       mydata.dbo.UpdateNAV
WHERE     (Date = ?) AND (PortfolioId = ?) AND (SecurityType in ?)

Then go to parameters tab and map parameters to your SSIS variables. Note that SSIS maps your SSIS variables to question marks in the query in the order you listed them.

Note: If You have many rows this package will be very slow. It is much better not to process row by row, but to change the logic of your package so it works with sets (for example, assign values in data flow and use oledb destination to insert data into MonthEndCDSSpreadCalc).