Solved

SSIS 2008, Number counter

Posted on 2010-09-09
5
739 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

813 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now