none
SSIS to load xml/xls to sql server RRS feed

  • Question

  • Hi All 

    I need some help to get an ETL logic to process files from source to target. We have a third party vendors who provides source files few in xml and few in excel. 

    They have a nightly job that drops files on to shared drive. 

    I need to build a prerequisite logic before i load the tables in to target as follows

    1) Check all the xml files  are latest or not. This can be done by checking the timestamp at the end of each file name. If the files are not latest do not load and send an email to customers.

    2) Check all the excel files are latest or not. Even though if any of these files are not latest we are okay to load. 

    How can i get this file info to loaded in to tables and do the checks.

    Thanks


    • Edited by Rajm0019 Monday, February 11, 2019 6:38 PM
    Monday, February 11, 2019 6:28 PM

All replies

  • Hi Rajm0019,

    "...Check all the xml files  are latest or not. This can be done by checking the timestamp at the end of each file name..."

    You can use SSIS Script task to do that.

    "...How can i get this [XML] file info to loaded in to tables..."

    You can use SSIS XML Source Adapter to do that.

    Monday, February 11, 2019 6:45 PM
  • Okay Can you help on some sample script code.

    Thanks

    Rajesh

    Monday, February 11, 2019 6:59 PM
  • Hi Rajesh,

    First, please provide samples of XML files naming convention.

    Monday, February 11, 2019 11:13 PM
  • Hi Rajesh, 

    What's definition of the latest file?

    Are you going to compare the timestamp in File Name with the value stored in a table, which will be updated after package execution? Or, the file will be moved to another folder after Data Flow Task? Then all the files in the folder will be latest. 

    Hope these links help:

    Get dynamic file name using foreach loop container SSIS

    Get file properties with SSIS


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, February 12, 2019 7:31 AM