Deriving Fiscal Year and Fiscal Month for an SSIS package

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

robboll - 08 Feb 2010 02:15 GMT

If I have an "Execute SQL Task" with sql that looks like this:

SELECT FLAG FROM "MyDB"."RUNorHOLD"
WHERE FY = '2010' AND PER = '4'

How can I turn '2010' and '4' into variables so it will reflect the
correct year and fiscal month regardless of when the package is run?

This may be easy but it shure has me going.

Thanks,

RBollinger

Reply to this Message

Todd C - 08 Feb 2010 13:59 GMT

Exactly what part of it are you having trouble with? The part that makes sure
you get "2010" and "4" no matter what, or the part where you want to load
those two values (whatever they may be) into package variables?

You could write your T-SQL statement as:
SELECT 2010 AS [FY], 4 AS [FM]

but then what is the point of even having the Execute SQL Task?

If you want to load these values into Package Variables, set the Result Set
of the Execute SQL Task to Single Row. Then on the Result Set page, add two
mappings, and name them 0 and 1.

Again, can you explain what you are trying to accomplish?

Signature

Todd C
MCTS SQL Server 2005

> If I have an "Execute SQL Task" with sql that looks like this:
>
[quoted text clipped - 10 lines]
> RBollinger
> .

Reply to this Message

ROBBOLL - 11 Mar 2010 14:38 GMT

Let me present this another way:

If the current Fiscal Year (FY) is 2010 and the current Fiscal Month
(PER) is 4

In a Script Task, what is the code necessary to derive that CurrFY = 2010
and CurrFM = 4 ?

Here is what I am trying to do:

There is a maintenance Table: tblMaint (When the column CLSD_FL turns to 1
(True) the data is avalable for processing and the package should run.

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

Currently I have two hard coded parameters and a results set set up:
oFY = 2010
oFM = 5
oFLAG (Result Set R/W string)

Each morning an Agent task checks to see if CLSD_FL changes to a 1 When it
changes to a 1 oFLAG is set to 1 and the job kicks off.

I am looking for the best method of setting this up so that it is not
necessary to manually change the parameters for each month.

Thanks for any help with this!

ROBBOLL

Reply to this Message