locked
Creating flatfile with dynamic filename? RRS feed

  • Question

  • I'm able to create a flat file by calling a stored procedure to return my data (via an ole db source) & dumping it into a flat file destination.  This all works successfully when I specify the name of the final txt flat file I create.  My problem however, is I need to create a dynamic filename but I'm not sure how.  The name has to be in the following format:

    MY_FILE_NAME_<timestamp>_<record count>.txt

    So I need to pass in the timestamp (YYYYMMDD) and the number of records in my flat file.  Any idea how I can do this?

    Thanks.

    Friday, September 9, 2011 8:39 PM

Answers

  • You have to fetch the number of records before you export the data. Create an Execute SQL Task, "select count(*) from table" and pass the output to a variable RecordCount.

    Create a new variable for the file name. In the Expression Editor do something like:

    @[User::Path] + "MY_FILE_NAME_" + (DT_WSTR, 4) YEAR( GETDATE()  )+right("00" + (DT_WSTR, 2)  month( GETDATE() ),2) +  right("00" + (DT_WSTR, 2)DAY( GETDATE() ),2)+ "_" + @[User::RecordCount]+".txt"

    Change the "EvaluateAsExpression"-Flag to true

    This varaible use in your Flatfile Connetion Manager as Connection

    • Marked as answer by Eileen Zhao Friday, September 16, 2011 9:40 AM
    Friday, September 9, 2011 9:21 PM