locked
Moving multiple files (xls, xlsx, txt, etc.) from a folder to multiple databases RRS feed

  • Question

  • So I am new to SSIS and I was just wondering how can I go about in importing files from a specific folder to multiple tables
    Monday, September 24, 2012 4:51 PM

Answers

  • For that you need to identify from the incoming source data which data is new data/ existing data. Existing data can either be updated or have the exact same values i.e they are unchanged. There are quite a few ways of doing that. Check this which gives an overview of 3 such approaches i.e. using merge join, using lookup or using script component.

    Also there is a third party tool from CozyRoc called Table difference (check here) which does all of the above in a single step. However note you will have to pay for a production license to use this component.


    http://btsbee.wordpress.com/

    • Marked as answer by Eileen Zhao Monday, October 1, 2012 7:18 AM
    Monday, September 24, 2012 10:58 PM

All replies

  • For each file type you would have to create a separate file connection. So a separate connection for flat files (txt, csv); a seprate connection for excel files (xls, xlsx). If the format of the files of a particulat type is the same then you can use the same connection to load data to mutliple destination tables (using mutlicast transformation). If the format of the files of a particulat type is NOT the same then you use different connections for each format or you use a script task to parse the file manually.


    http://btsbee.wordpress.com/

    Monday, September 24, 2012 5:00 PM
  • Thanks for your help.

    How do I go about in making sure that everytime I run this package duplicate records won't be created?

    Monday, September 24, 2012 8:45 PM
  • For that you need to identify from the incoming source data which data is new data/ existing data. Existing data can either be updated or have the exact same values i.e they are unchanged. There are quite a few ways of doing that. Check this which gives an overview of 3 such approaches i.e. using merge join, using lookup or using script component.

    Also there is a third party tool from CozyRoc called Table difference (check here) which does all of the above in a single step. However note you will have to pay for a production license to use this component.


    http://btsbee.wordpress.com/

    • Marked as answer by Eileen Zhao Monday, October 1, 2012 7:18 AM
    Monday, September 24, 2012 10:58 PM