Search code examples
sql-servervariablesssisetlssis-2008

How to pass variables in data flow task SSIS 2008 r2

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

Source Connection

But when I click on the Parameters button it throws error, Parameters 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. Variables

Solution

  • 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