SSIS 2008, Number counter

SSIS Gurus,

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.

Can you please help

Microsoft SQL ServerMicrosoft SQL Server 2008

Last Comment

banjara15

8/22/2022 - Mon

vdr1620

9/9/2010

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

banjara15

9/10/2010

ASKER

Thanks for the prompt reply vdr1620.

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

ASKER CERTIFIED SOLUTION

vdr1620

9/10/2010

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Reza Rad

9/11/2010

what do you mean by : files exists on client?!
explain more?
where you will find files and based on them R value?
explain with samples

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

banjara15

9/17/2010

ASKER

vdr160, my apologies for the late response. I tried your solution and worked fine and resolved my problem. Thanks again for your prompt reply and help.. :)