Post reply

data load from excel spreadsheets automatically

  • August 14, 2009 at 3:23 pm

    #209427

    I am trying to load the data using SSIS(sql 2005) from about 5 excel spreadsheets automatically based on the

    following condition:

    When ever the user updates those excel spreadsheets, the SSIS package should take those spreadsheets and automatically

    load the data into the table. I am not sure how to do this, please let me know how I can do this using SSIS packages.

    Thanks much!

  • August 17, 2009 at 2:16 am

    #1040052

    I have been contemplating similar. My approach is to have an 'inbound' folder where people will place the spreadshhet to be loaded. The SSIS package runs a for each loop containber. It looks for files in the inbound folder. If it doe not find any files it finishes. If it finds file then in the for each loop it will

    1. use one or more data flow tasks to load the data.

    2. move the file to 'done' folder.

    This works if the files are in the same format and destination. If they are not then its a little more thought required.

    Hope that helps.

    Ells.

    😎

  • August 17, 2009 at 6:15 am

    #1040113

    There are many ways to skin a cat, have you thought of using vbscripting to update the sql database directly at the click of a button. You could build a macro that would take the data and insert it into the table you require.

    Here is a link I found using Google http://database-programming.suite101.com/article.cfm/how_to_use_sql_statements_with_vbscript that might help you with that approach.

    If however, there is no requirement for you to be updating during the day everytime the user saves the file, ie. you can run the package at night for the next day. Then Ells idea will work like a charm.

    If you need to update during the day, and can't use vbscripting, you are looking at a lot of work. File watcher tasks, stored procedures to check data integrity, messaging to users that haven't saved the spreadsheets in the correct format, etc.

    Good Luck! 😉

    ----------------------------------------------------------------------------------------------
    Struggling to find the question?
    http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

  • August 17, 2009 at 6:32 am

    #1040125

    I should have added:

    Having created a package to check and load this could be scheduled through SQL Server Agent to run every xx minutes.

    I have to admit I like the click of a button that way the users can send the spreadsheet and request processing.

    Ells

    😎

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply