none
Output in separate xl file on daily basis RRS feed

  • Question

  • Hi,

    How to configure the package to output the results in separate XL sheet daily ?

    I did disable 'Overwrite data in the file' option from the Flatfile destination control. but still its overwriting the file like its writing the results immediately after the last line of the file.

    i want it to be written in a separate XL file.. If any one knows it please let me know !

    Thanks !


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------


    • Edited by Radhai Tuesday, August 28, 2012 10:00 AM
    Tuesday, August 28, 2012 9:57 AM

Answers

All replies

  • Hi Radhi,

    The simple is to add a date stamp at the end of excel file name. Kindly have a look at below link to to see how to do this.

    http://www.sqlnerd.com/ssis_dynamic_dates.htm

    This link shows this for text file but same method can be used for excel files.


    - Chintak (My Blog)

    • Proposed as answer by AB82Moderator Tuesday, August 28, 2012 10:42 AM
    • Marked as answer by Radhai Tuesday, August 28, 2012 10:56 AM
    Tuesday, August 28, 2012 10:13 AM
  • If you are using the same file name then it will overwrite on each run. You can either use a unique file name on each run for e.g. as Chintak mentioned append the current datetime to the filename to make it unique. However if you still want to use the same file name then you will need a step where you either move/ delete the old file.


    http://btsbee.wordpress.com/

    Tuesday, August 28, 2012 10:34 AM
  • Hi

    I also have the same requirement. I've implemented as suggested but I am getting the below error.

    Nonfatal error occured while saving the package:

    Error at SAPBICustomer [Connection Manager "Excel Connection Manager"]: The connection string format is not valid. it must consist of one or more components of the form X=Y, seperated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

    Error at SAPBICustomer: The result of the expression ""C:\\Users\\Public\\Documents\\SAP BI\\SAPBI"+(DT_STR), 8, 1252)
    ( YEAR(GETDATE()) * 10000 + MONTH( GETDATE()  ) * 100 + DAY(GETDATE())) + ".xlsx"" on property "connection string" cannot be written to the property, The expression was evaluated, but cannot be set on the property.

    Please help.

    Tuesday, August 28, 2012 10:44 AM
  • Noor,

    Thats because the expression you have used to set your filename is incorrect. It has an additional bracket in it. Use this expression "C:\\Users\\Public\\Documents\\SAP BI\\SAPBI" + (DT_STR,8,1252)(YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + DAY(GETDATE())) + ".xlsx" to set your filename.

    Also in future if you have errors please create a new thread as posting on someone elses thread simply hijacks the original issue raised in the thread.


    http://btsbee.wordpress.com/


    • Edited by btsbee Tuesday, August 28, 2012 10:53 AM
    Tuesday, August 28, 2012 10:52 AM