[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SSIS 2008, Number counter

Posted on 2010-09-09
5
Medium Priority
?
756 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

656 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