including a count in the file name

  • 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 and so on and so forth.

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

  • In many of the databases that I work with, we have a configuration table, that store information that is needed for various tasks. You could create such table and have one of the things that you store in it, the file's number. Each time after you create a file, you'll add 1 to this value.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • hewitg 82815 (5/13/2013)


    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 and so on and so forth.

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

    You obviously can't do this in advance (because you don't yet know the row count). So you have to generate the file (use a generic file name), capture the row count and then copy the file to its new name.

    1) Create an integer package-scoped variable.

    2) Add a row count transform in your data flow to count the number of rows and assign it to the above variable.

    3) Create a calculated variable to hold the filename & create the expression to populate it using the above variable contents, as required.

    4) Use a File System Task (or script task) after the data flow to copy the created file to the name held in the dynamic variable above.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply