SSIS 2008, Number counter

Posted on 2010-09-09
Medium Priority
Last Modified: 2013-11-10

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
Question by:banjara15
  • 2
  • 2
LVL 16

Expert Comment

ID: 33643414
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

Author Comment

ID: 33648531

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

Accepted Solution

vdr1620 earned 2000 total points
ID: 33648988
Create a Table in your database ..Something Like

Create Table TableName
Id INT Identity(1,1),
RunNumber Int,
MonthNumber Int  
Date Column             -- If necessary

Then in the Execute SQL task use this SQL to get the RMM Number

SELECT CASE WHEN MAX(RunNumber) IS NULL  OR MAX(RunNumber)  = 9 THEN '1' + RIGHT('0'+Convert(Varchar(2),Month(GETDATE())),2) ELSE
Convert(Varchar(2),MAX(RunNumber)+1) + RIGHT('0'+Convert(Varchar(2),Month(GETDATE())),2) END as FileExtension
FROM TableName

In general tab of execute SQL task set the Value to SingleRow
In resultSet Tab give a ResultSetName and create a variable with a String Data Type Like FileNameExt (which will store the RMM number)

Create another Variable FileName with FileName(Complete Path of the FileName) and at the end add previously created variable

Ex: "C:\\FolderName\\FileName"+"."+@FileNameExt

Use this Variable in the Connection string Property of the Text File using the Expression editor..So that it creates the File with that name on runtime
Attach the execute SQL task to your DataFlow Task and Execute the package

LVL 30

Expert Comment

by:Reza Rad
ID: 33655674
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

Author Closing Comment

ID: 33703389
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.. :)

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question