Solved

SSIS 2008, Number counter

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

930 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

12 Experts available now in Live!

Get 1:1 Help Now