locked
Creating a new excel file dynamically for starting of every month and moving the old month file to archive folder using ssis RRS feed

  • Question

  • Hi all,

    I have loading the data in excel file on daily basis to create the new tab[sheet] on daily based on getdate.

    Now my requirement is I want to create a new Excel file dynamically for starting of every month and create the new tab[sheet] to load the data on daily based on getdate.

    After end of every month the old month file to archive folder.

     

    All BI experts please help me on this ASAP


    Arun

    Tuesday, June 4, 2013 4:49 AM

Answers

  • I am not goot at SSIS. still would like give you the steps.

    1. Use sql task to find first day of current month.

    2. Create Excel File with filename Suffixed Current date stamp.

    3. If last day of current month, move the file to new location using move cmd command.

    DECLARE @mydate DATETIME SELECT @mydate = GETDATE() SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) , 'Last Day of Previous Month' SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value, 'First Day of Current Month' AS Date_Type



    Srinivasan

    • Proposed as answer by Mike Yin Friday, June 7, 2013 3:30 AM
    • Marked as answer by Mike Yin Tuesday, July 9, 2013 7:40 AM
    Tuesday, June 4, 2013 4:56 AM
  • easiest is probably to store an empty version of your excel file and then use the File System Task to copy it to the destination location of your Data Flow Task.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Proposed as answer by Mike Yin Friday, June 7, 2013 3:30 AM
    • Marked as answer by Mike Yin Tuesday, July 9, 2013 7:40 AM
    Tuesday, June 4, 2013 5:20 AM

All replies

  • I am not goot at SSIS. still would like give you the steps.

    1. Use sql task to find first day of current month.

    2. Create Excel File with filename Suffixed Current date stamp.

    3. If last day of current month, move the file to new location using move cmd command.

    DECLARE @mydate DATETIME SELECT @mydate = GETDATE() SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) , 'Last Day of Previous Month' SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value, 'First Day of Current Month' AS Date_Type



    Srinivasan

    • Proposed as answer by Mike Yin Friday, June 7, 2013 3:30 AM
    • Marked as answer by Mike Yin Tuesday, July 9, 2013 7:40 AM
    Tuesday, June 4, 2013 4:56 AM
  • easiest is probably to store an empty version of your excel file and then use the File System Task to copy it to the destination location of your Data Flow Task.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Proposed as answer by Mike Yin Friday, June 7, 2013 3:30 AM
    • Marked as answer by Mike Yin Tuesday, July 9, 2013 7:40 AM
    Tuesday, June 4, 2013 5:20 AM