How to pass variables in data flow task SSIS 2008 r2

Question

I am trying to transfer data from one OLEDB connection to another OLEDB. SSIS and SQL Server both 2008 R2. I want to use a variable to load the data for each year (2014, 2015 so on). SQL query in the source is,

DECLARE @year int set @year = ?
SELECT [ServiceActivities]
      ,[ServiceID]
      ,[ClientID]
      ,[WorkerID]
      ,[CostCode] 
      ,[ProviderID]
      ,[CostUnit]
      ,[Units]
      ,[OccurrenceDate]
FROM [dbo].[PlannedAppointmentsView] 
WHERE datepart (yy, OccurrenceDate) = @year

But when I click on the Parameters button it throws error,

I have created a variable, not sure if i need one. How can I automate the process to change the variable for each year till current year? Thanks to All.

Answer 1

Try to avoid declaring a variable, just place the ? in the direct place that you need to pass a parameter.

SELECT [ServiceActivities]
  ,[ServiceID]
  ,[ClientID]
  ,[WorkerID]
  ,[CostCode] 
  ,[ProviderID]
  ,[CostUnit]
  ,[Units]
  ,[OccurrenceDate]
FROM [dbo].[PlannedAppointmentsView] 
WHERE datepart (yy, OccurrenceDate) = ?

If it still doesn't working, then you must follow the suggestion given to you in this error message.

Just Create a variable (ex: strQuery) of type String, go to the Properties Tab, change the Evaluate as expression property to True, and assign the a similar expression to this variable:

"SELECT [ServiceActivities]
  ,[ServiceID]
  ,[ClientID]
  ,[WorkerID]
  ,[CostCode] 
  ,[ProviderID]
  ,[CostUnit]
  ,[Units]
  ,[OccurrenceDate]
FROM [dbo].[PlannedAppointmentsView] 
WHERE datepart (yy, OccurrenceDate) = " + (DT_WSTR,50)@[User::year]

after that in the OLEDB Source select SQL command from variable option and choose this variable