SSIS : Passing variables to sql

Creating Sql strings to pass variables through to Sql Server >

T-SQL
"SET FMTONLY OFF; SELECT TOP "+ (DT_STR, 8,1252) @[User::RowBatchSize] +" * FROM [dbo].[sourceData] WHERE loadId > " + (DT_STR, 8,1252) @[User::LastProcessedID]

Executing a Stored Procedure
"SET FMTONLY OFF; EXEC dbo.dataLoad @rows = "+ (DT_STR, 8,1252) @[User::RowBatchSize] +",@loadId = "+ (DT_STR, 8,1252) @[User::LastProcessedID]

To use this method >
1) Create a variable
2) Set the variable property 'EvaluateAsExpression' to 'True'
3) Set the expression with your sql statement (see examples above)

You can use this in either the Control flow or Data Flow.

In a Control Flow >

i. Add an Execute SQL Task
ii. Set the 'SQL Source Type' to 'Variable'
iii. Set the SourceVariable to the variable name

or

In a Data Flow >

i. Add a OLE DB Source and open the OLE DB Source Editor.
ii. Set 'Data Access Mode' to 'SQL Command from Variable'.
iii. Set the 'Variable Name' to ooh.... the name of the variable containing the expression!