none
SSIS question

    Dotaz

  • Hi,

    I am trying to develop a package with below requirement.

    We have an existing package that generates a file daily hitting against SQL and Teradata tables. The file name has current date and they are placed in a separate file server. Whenever there is an outage or some other environmental issues the file doesn't generate for that day. In these cases, we need to insert dates in our table and then execute the job such that the job runs and generates file for the missing dates.

    So, I am thinking if we can develop a package that connects to file server and checks for the missing file's date and inserts the missing date into the table on database server and generates the file. 

    Any suggestions appreciated! Thanks in advance!


    harshac

    čtvrtek 17. května 2018 19:33

Všechny reakce

  • So are you preserving the files somewhere after processing each day? Otherwise how will you retrospectively find the missing files?

    The way we do it is to preserve details of the files processed in a control table

    Then everytime there was issue, on fixing and restarting the job, it iterates through all files available in the folder and check against control table to see if it was processed successfully earlier and will only pick the ones without a successful entry in the table (record with status success)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    čtvrtek 17. května 2018 19:43
  • i think you could add a script task and  c# or vb to check for file availability and create a new file if isn't available.

    https://www.sqlservercentral.com/Forums/Topic1575060-364-1.aspx


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Upravený Sarat (SS) čtvrtek 17. května 2018 19:45
    čtvrtek 17. května 2018 19:44
  • Hi visakh,

    Thanks for your response!

    Files are processed and kept in a folder on our file server. I am not sure about the control table. Could you please elaborate how you do it?


    harshac

    pátek 18. května 2018 20:19
  • Hi visakh,

    Thanks for your response!

    Files are processed and kept in a folder on our file server. I am not sure about the control table. Could you please elaborate how you do it?


    harshac

    Just adda a table in your db with filename, processed date and status (success/failed). Then you can rely upon the table to identify missed/failed files to be reprocessed

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    pátek 18. května 2018 21:20