Unable To Create Variable Select Statement In For Each Loop

What I'm trying to do is this;

I have a table with Year , Account and Amount as fields. I want to



SELECT Year, Account, sum(Amount) AS Amt

FROM GLTable

WHERE Year <= varYear



varYear being a variable which is each year from a query



SELECT Distinct Year FROM GLTable



My thought was that I would need to pass a variable into a select statement which then would be used as the source in my Data Flow Task.



What I have done is to defined two variables as follows

Name: varYear (this will hold the year)

Scope: Package

Data type: String



Name:vSQL (This will hold a SQL statement using the varYear)

Scope: Package

Data type: String

Value: "SELECT Year, Account, sum(Amount) AS Amount FROM GLTable WHERE Year <=" + @[User::varYear]



I've created a SQL Task as follows

Result set: Full Result Set

Connection Type: OLE DB

SQL Statement: SELECT DISTINCT Year FROM GLTable

Result Name: 0

Variable Name: User::varYear



Next I created a For Each Loop container with the following parameters

Enumerator: Foreach ADO Enumerator

ADO Object source Variable: User::varYear

Enumeration Mode: Rows in First Table



I then created a Data Flow Task in the Foreach Loop Container and as the source used OLE DB Source as follows

Data Access Mode: SQL Command from Variable

Variable Name: User::varYear



However this returns a couple of errors "Statement(s) could not be prepared."

and "Incorrect syntax near '='.".



I'm not sure what is wrong or if this is the right way to accomplish what I am trying to do. I got this from another thread "Passing Variables" started 15 Nov 2005.



Any help would be most appreciated.

Regards,

Bill