CODEWITHSUNDEEP
sqlssis
GPH
GPH

Reputation: 1897

SSIS filename - file count

I'm currently creating a flat file export for one of our clients, i've managed to get the file in the format they want, i'm trying to get the easiest way of creating a dynamic file name. I've got the date in as a variable and the path ect but they want a count in the file name. For example

File name 1 : TDY_11-02-2013_{1}_T1.txt. The {} being the count. So next weeks file would be TDY_17-02-2013_{2}_T1.txt

I cant see an easy way of doing this!! any idea's??

Upvotes: 1

Views: 2359

Answers (2)

Diego
Diego

Reputation: 36146

EDIT: on my first answer, I thought you meant count of values returned on a query. My bad! two ways to achieve this, you could loop into the destination folder, select the last file by date, get its value and increase 1, which sound like a lot of trouble. Why not a simple log table on the DB with last execution date and ID and then you compose your file name base on the last row of this table?

where exactly is your problem?

you can make a dynamic file name using expressions:

enter image description here

the count, you can use a "row count" component inside your data flow to assign the result to a variable and use the variable on your expression:

enter image description here

Upvotes: 1

praveen
praveen

Reputation: 12271

Use Script task and get the number inside the curly braces of the file name and store it in a variable. Create a variable(FileNo of type int) which stores the number for the file

Pseudo code

  string name = string.Empty;
  string loction = @"D:\";

 /* Get the path from the connection manager like the code below
    instead of hard coding like D: above
   string flatFileConn = 
   (string(Dts.Connections["Yourfile"].AcquireConnection(null) as String);
 */
  string pattern = string.Empty;
  int number = 0;
  string pattern = @"{([0-9])}"; // Not sure about the correct regular expression to retrieve the number inside braces
  foreach (string  s in Directory.GetFiles(loction,"*.txt"))
     {
        name = Path.GetFileNameWithoutExtension(s);
        Match match = Regex.Match(name, pattern );
          if (match.Success)
           {
             dts.Variables["User::FileNo"].Value = int.Parse(match.Value)+1;
           }
     }

Now once you get the value use it in your file expression in the connection manager 

@[User::FilePath] +@[User::FileName]
                             +"_{"+ (DT_STR,10,1252) @[User::FileNo] + "}T1.txt"

Upvotes: 0

Related Questions