If you're using SSIS for the output, then you can use the same SSIS package to read in data from a text file and use it in a query to pull data from the database and spit that pulled data out into another flat files.

The easiest might be to tell the users to put just one single text file into a certain directory, and give that text file the year that they want - no file then the current year. Then you use a foreach construction to pull in all (one of) the text files. In the foreach, have it set a variable to just the file name (not path or extension). Once that's done you can use the variable in some SQL to pull the desired data .....

You have a pretty strange concept.
Load data for all years to DB and make
report with parameters in Excel, SSRS, Access and so on.
What you are talking about 20 years old approach then
first PC was used mostly as typewriters.

If you're intent on reading a value from a file, I agree with Alun. Insure that the value is reasonable and changed by someone authorized to do so by controlling the entry and storage.

I'd probably try to minimize yearly FY change problems by including a change date. That way the FY change would happen on schedule without intervention. Some way of letting the user know when the FY change would be included at least from just before the earliest valid change date until the change took place. The app would only be used to advance or delay the change date or revert if intended delay was not updated in time. If the automatic part of that method worked well enough (or you thought it would), you might skip the user update feature for now. I've had situations where the year or FY close out was extremely variable, but in those cases, the users and IT support were in close communication due to the activities that had to take place timely and quickly. In those cases, DBAs made the changes as soon as authoriztion was received from the appropriate user.

What I suggested - using the name of a file to determine what you want to retrieve - takes a simple piece of SSIS. However, if you want to provide a readable report that can also be provided in Excel or PDF format, break out SSRS, do the report off the query you already have (in a stored procedure, with parameters), put a parameter or two onto the report to control the sp, and let the user decide what he or she wants to see, see it, decide that it is what they want, and then export it out to Excel, PDF, etc.

I am sorry if I misinterpreted myself by saying reporting, there is no involvement of pdf or excel or access or webpage or anything else. I can do this in DataStage but since this project is so simple I just want to use a SSIS package instead of DataStage.

I do not want to add any more complexities. Its very simple what I need to deliver:
Source- SQL Table
Target- Text file
Process -Use another text file with a fiscal year in it (FYear.txt) & use it as a WHERE clause on source SQL.

Put all of this in a SSIS package that would spit a flat file.

SSIS package will include:
Select Source_table
where Year =(FYear.txt)

Thanks Brett
That is exactly what I am trying to do but since this is my first SSIS package that I am creating, it would be helpful if you have some syntax. I can do this in DataStage but I am just not that familiar with SQL commands to do the same.

Start BIDS and start a new SSIS package. Add a variable (call it strFileName). From the toolbox add a ForEach task. Right-click the task and select edit. Fill in the fields so that you tell it the path, the extension (.txt), and that you want just the name (not path or ext). Go to the Parameters tab and set the zeroth parameter to be mapped to your variable. This should get you the file name of any txt files in your directory. Tell the users to put a file called 2009.txt there if they want data from 2009.

Next, add a DataFlow Task from the toolbox and connect the green output from the for each task to it. Double-click the dataflow task.

Now you will be in the DataFlow window. Add a SQL task from the toolbox. Link it to the green output of the foreach task. In the SQL you should be able to write a query like
"select * from table where year = " + User::strFileName

Then get an Excel Destination from the toolbox, connect the green output from the SQL task to the Excel destination, open up the Excel destination, and work through getting it a file to send the data to. You should be able to use User::strFileName again to identify the file content.

Please excuse any errors here - it's all from memory as I don't have SSIS up in front of me!

"Not meaning to hi-jack, but how does one make AN SSIS package on-demand for users? "

1. create a program that will kick off a SQL Agent job that runs the SSIS job.
2. Have a single SSIS job that runs every few minutes looking in a table or in a folder for things to do, and which sets off other jobs as a result.

For #1, I think PragmatiWorks has an example somewhere on their site.

As an example of #2, we have an email job running every 2 mins that looks in a table to see if any other process has dropped in a record with info to send out via email. That way just one process gets the privileges needed for email o/p and if problems happen just one program has to be debugged and we don't lose the email data - it's all still there in the request table.

I'm sure there are things in .net to do lots of complicated and wonderful things, but I believe in simplicity - I have enough problems to fix without them being difficult ones :)