none
SSIS add last row to a flatfile RRS feed

  • Question

  • Does any body know how add a row into flatfile created with SSIS..?

    I have the following generated file:

     

    01     aaa    bbb    30    ccc

    02     bbb    ccc    20    ddd

    .

    .

    .

    .

    10    eee    fff      20    jjj

    filename.txt    createdate    10     70

     

    At the end of all row i have to add a new row that show the filename, date creation of the file, quantity of rows, and total sum of the forth column...i have data from a view and also i created a column in the view that has the row that i need(filename.txt    createdate    10     70), but i dont know how transfer that value to the end of the file.

    can i do this with SSIS?

     

    thanks for any comment

     

    Tuesday, August 19, 2008 6:38 PM

Answers

All replies

  • There's an example I created here: http://agilebi.com/cs/blogs/jwelch/archive/2008/02/08/adding-headers-and-footers-to-flat-files.aspx

     

    It shows using a script source with a second flat file destination to append the footer.

    Wednesday, August 20, 2008 12:41 AM
    Moderator
  • Hi,

    Create two data flows linkied by a precedence constraint.

    The first data flow contains a flat file destination with Override data in the file

    The second data flow contains a flat file destination that uses the same connection but with no Override data in the file and a different mapping.

    When you define the destination connection, it must define as many or more columns than you have in the second part.

    Wednesday, August 20, 2008 1:17 AM
    Moderator
  • Thanks guys,,,finally i did it because your help......but know i'm trying to rename a file created with SSIS...

    i have to do this.:

     

    filename.txt ------> filename_YYYYMMDD.txt

     

    where YYYYMMDD is the date when the file was created..

     

    Wednesday, August 20, 2008 4:50 PM
  • Use the FileSystem task, you can set the source and the new name through variables.

     

    BTY, there is also a free task called File Watcher that you can get from www.konesans.com

    I like this because it is less intrusive than FileSystem task when you just want to check the existence of a file.

     

    Thanks to mark the correct answer as the answer

    Wednesday, August 20, 2008 5:12 PM
    Moderator
  • You can also set the filename dynamically by using an expression on the flat file connection manager's connectionstring property. You can easily add the current date to the filename by doing this.

     

    Jamie shows the technique here: http://blogs.conchango.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx

     

    Wednesday, August 20, 2008 6:05 PM
    Moderator
  • i'm trying to do it with FileSystem task but i'm a little confused with the File System Task Editor window at the DestinationConnection and SourceConnection fields.....what exactly means that options... and in the Expressions option what property should i use.....i think this must be simple but i start to use it a few days ago...

     

    thank for the comments...

     

    Wednesday, August 20, 2008 8:32 PM
  • SourceConnection - filename.txt 

    DestinationConnection - filename_YYYYMMDD.txt

     

    The corresponding properties in the expression window will be source and destination.

     

    Hope this helps

    Senthil

     

    Wednesday, August 20, 2008 9:04 PM
    Answerer
  • Senthil

    thanks for answer.....i tried that you says but a have a error message that show an error cos the filename_YYYYMMDD is not found,,,,,and it supose is ok not found because in the folder exists the file filename.txt and this file should change the name to filename_YYYYMMDD.txt......i don't know what i'm doing wrong......

    In operation field i chose Rename file option

     

     

    Davidson

    Wednesday, August 20, 2008 9:49 PM
  • When you create the destination file connection manager, make sure to select "Create new file" for "Usage Type" property.

    Wednesday, August 20, 2008 10:09 PM
    Answerer
  • i'm so close,,,the file rename is working, but just one detail left........the new name is receiving the AAAAMMDD in static mode, i mean, is taking the name of the file connection....i think the better way is filling a variable and use IsDestinationPathVariable option in true...

    Davidson

    Wednesday, August 20, 2008 10:53 PM
  • i'm trying to do it with variables,,,,i created a sqltask and created a variable too,,,,,i assigned to that variable "Name_AAAAMMDD" by a sql statement, but when i back to FileSystemTask and choose the variable for DestinationVariable appears a red symbol that says "variable is used as a source o destination and is empty"...i don't know if i'm doing rigth...

    thanks for comments...

    Davidson

    Thursday, August 21, 2008 4:21 PM
  • To avoid this type of validation error, manually affect a value to the variable. This value is used to develop and validate the task and will be replaced by the values provided by your SQL task at runtime.

    Thursday, August 21, 2008 5:00 PM
    Moderator
  • Finally i did it......

    the file was created and renamed at runtime....

    I really appreciate all your comments that help me so much with my work...

    Best regards...

    davidson

    Friday, August 22, 2008 6:51 PM
  • This post describes how to do what you are talking about in detail:

    http://dataintegrity.wordpress.com/2010/01/22/extract-variable-records-to-the-same-file/

    JS

    Friday, January 22, 2010 11:19 PM