OleDB Source And Parameters

Hi,



Firtsly - I am new to SSIS if my approach could be improved then I welcome suggestions.



Scenario: I have a large SSIS package that consolidates / summarizes work week information from several data sources. Currently each data flow task in the control flow calculates the from and to date that is filtered on, for example:



DECLARE @FromDT AS DATETIME
SET @FromDT = CAST(FLOOR( CAST( DATEADD(D, -7, GETDATE()) AS FLOAT ) ) AS DATETIME)

DECLARE @ToDT AS DATETIME
SET @ToDT = CAST(FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME)



I would like to remove these statements that appear in most steps and replace them with a global variable that is used throughout the package. This statement would only appear once & it would make the package much easier to run after failure etc.



Problem: I am using Data Reader Source with the 'SQLCommand' property specified. It looks like parameters are only supported if an OleDB connection is used?



So I switched to an OleDB connection and no parameters are recognised in the string - a forum search reveals that parameters in sub queries are not always found properly. The solution to this problem appears to be, to set 'Bypass Prepare' to True but this is a property for the Execute SQL task, not the Data Flow Task source.



Questions:

Does the Data Reader Source control from Data Flow Source toolbox section support parameters?
Can anyone suggest a fix to the OleDB Source issue with Parameters?
Is there a better way to solve my problem e.g. Using Execute SQL Task instead of Data Flow tasks etc

Example SQL:

This SQL is an example of the SQL for the OleDB Data Source (within a Data Flow task)



------------------------------
--RADIUS LOGINS
------------------------------
DECLARE @FromDT AS DATETIME
SET @FromDT = CAST(FLOOR( CAST( DATEADD(D, -7, GETDATE()) AS FLOAT ) ) AS DATETIME)



DECLARE @ToDT AS DATETIME
SET @ToDT = CAST(FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME)



DECLARE @Attempts AS BIGINT
SET @Attempts =
(SELECT COUNT(*)
FROM dbo.Radius_Login_Records
WHERE LoggedAt BETWEEN @FromDT AND @ToDT)



DECLARE @Failures AS BIGINT
SET @Failures =
(SELECT COUNT(*)
FROM dbo.Radius_Login_Records
WHERE LoggedAt BETWEEN @FromDT AND @ToDT
AND Authen_Failure_Code IS NOT NULL)



DECLARE @Successes AS BIGINT
SET @Successes = @Attempts - @Failures

DECLARE @OcaV1Hits AS BIGINT
SET @OcaV1Hits = (SELECT COUNT(DISTINCT LoginName)
FROM dbo.Radius_Login_Records
WHERE LoggedAt BETWEEN
@FromDT AND @ToDT
AND EAPTypeID = 25)



DECLARE @OcaV2Hits AS BIGINT
SET @OcaV2Hits = (SELECT COUNT(DISTINCT LoginName) AS OcaV2Hits
FROM dbo.Radius_Login_Records
WHERE LoggedAt BETWEEN
@FromDT AND @ToDT
AND EAPTypeID = 13)


SELECT
@Attempts AS ConnectionAttempts,
@Failures AS ConnectionFailures,
(CAST(@Successes AS DECIMAL(38,2)) / CAST(@Attempts AS FLOAT) * 100) AS SuccessRate,
@OcaV1Hits AS OcaV1Hits,
@OcaV2Hits AS OcaV2Hits



Please remember, I'm new to SSIS - so be detailed in your response. Thanks for your help!