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.
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