Solved

SSIS 2008, Number counter

Posted on 2010-09-09
5
744 Views
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
0
Comment
Question by:banjara15
  • 2
  • 2
5 Comments
 
LVL 16

Expert Comment

by:vdr1620
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
0
 

Author Comment

by:banjara15
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..
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 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
WHERE MonthNumber = MONTH(GETDATE())

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


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

Author Closing Comment

by:banjara15
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.. :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

789 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