CODEWITHSUNDEEP
sql-servervariablesssisetlssis-2008
MohammedS
MohammedS

Reputation: 221

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

Upvotes: 2

Views: 11346

Answers (2)

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

You whould just write like this

declare @Year int = ?

And then press Parameters button and select your desired parameter. Then it will work.

But the right way to do it, is like @Hadi writes in his first comment.

Upvotes: 1

Hadi
Hadi

Reputation: 37313

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

Upvotes: 2

Related Questions