Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

data load from excel spreadsheets automatically Expand / Collapse
Author
Message
Posted Friday, August 14, 2009 3:23 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 2:48 PM
Points: 262, Visits: 1,503
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!
Post #771353
Posted Monday, August 17, 2009 2:16 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 9:59 AM
Points: 692, Visits: 754
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.
Post #771751
Posted Monday, August 17, 2009 6:15 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 14, 2011 3:01 AM
Points: 95, Visits: 308
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
Post #771828
Posted Monday, August 17, 2009 6:32 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 9:59 AM
Points: 692, Visits: 754
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
Post #771843
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse