I have an "Execute SQL Task" that has hard coded values like:
SELECT CLSD_FL FROM "AMS_FINUSER"."R_APD"
WHERE FY = '2010' AND PER = '4'

FY = Fiscal Year (Oct-Sep) and PER = Fiscal Month (January)


Both FY and PER can be derived using VBA.


This code works to derive the Fiscal Year and Fiscal Month in an MS
Access module using VBA but
does not work in SSIS:


----------------------------------------------------------- start


Option Compare Database


Option Explicit
Const FMonthStart = 10 ' Numeric value representing the first
month
' of the fiscal year.
Const FDayStart = 1 ' Numeric value representing the first
day of
' the fiscal year.
Const FYearOffset = -1 ' 0 means the fiscal year starts in the
' current calendar year.
' -1 means the fiscal year starts in
the
' previous calendar year.


Function GetFiscalYear(ByVal X As Variant)
If X < DateSerial(Year(X), FMonthStart, FDayStart) Then
GetFiscalYear = Year(X) - FYearOffset - 1
Else
GetFiscalYear = Year(X) - FYearOffset
End If
End Function


Function GetFiscalMonth(ByVal X As Variant)
Dim m
m = Month(X) - FMonthStart + 1
If Day(X) < FDayStart Then m = m - 1
If m < 1 Then m = m + 12
GetFiscalMonth = m
End Function


------------------------------------------------------ End


================================================== ============
Here's everything I am trying to accomplish:


There is a maintenance table called r_apd with values like:


FY PER CLSD_FL
2009 11, 1
2009 12 1
2009 13 1
2010 1 1
2010 2 1
2010 3 1
2010 4 0 <-- current
2010 5 0
2010 6 0


Whenever all data is available for that FY/PER, the CLSD_FL (Closed/
Final) value changes to a 1


I am trying to set up the SSIS job so that if FY = '2010' and PER =
'4' and CLSD_FL = '1' it kicks off the job.


So the Execute Task use variables and look something like:


SELECT CLSD_FL FROM "AMS_FINUSER"."R_APD"
WHERE FY = mFY AND PER = mPER;


And if CLSD_FL = 1 it runs the job.


Any help or references on how to use the above VBA in an SSIS package
appriciated!


Thank you!


RBollinger