Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

including a count in the file name Expand / Collapse
Author
Message
Posted Monday, May 13, 2013 8:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 5:53 AM
Points: 3, Visits: 60
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??
Post #1452131
Posted Monday, May 13, 2013 8:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:35 AM
Points: 2,114, Visits: 5,428
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/
Post #1452141
Posted Monday, May 13, 2013 8:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:23 AM
Points: 5,047, Visits: 11,799
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1452159
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse