SSIS 2008, Number counter

I am generating a fixed width flat file using SSIS 2008 and my problem is with the file naming.
As per requirement the file extension should be in "RMM" format. Where "R" is the run number which should be always between 1 - 9 and "MM" is for the month in which the file was generated.

These are some examples for a better understanding:
1. If the file got generated on 08/01/2010, the file name should be "XYZ.108"
Here 1 => first file for the month of 08

2. If the file again gets generated on 08/15/2010, the file name should be "XYZ.208"
Here 2 => second file for the month of 08

3. If the file again gets generated on 08/18/2010, the file name should be "XYZ.308"
Here 3 => third file for the month of 08

4. If the file gets generated on 09/02/2010, the file name should be "XYZ.109"
Here 1 => first file for the month of 09

My problem is how to keep track of the Run number used earlier so I could increment it by 1 for the next file. The range for the Run number is from 1 - 9. After 9 it should go back to 1 again.

i would say use a table to keep track of the Run Numbers..there's no way you keep track of such numbers in the package directly.. The other way around is to check the FileName each time you run the Package based on the month using a script task and then use another variable to increment the value..

what if you have more than 10 files in a month ..what would be the name of the run number??

let me know..so that i can help you with the rest of the process, if you need

The Run number range is 1 to 9. If there are more than 9 files in a month then the run number should go back to 1. So there will be two files with the same name in a month. As these files will get ftped to the client everyday, it is upto them to decide which file to use. They don't think that it will ever happen though..

I'll really appreciate if you could help me in the rest of the process..

Featured Post

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf