Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


data load from excel spreadsheets automatically


data load from excel spreadsheets automatically

Author
Message
Mh-397891
Mh-397891
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 1514
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!
Ells
Ells
Right there with Babe
Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)

Group: General Forum Members
Points: 790 Visits: 931
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.
Cool
WilliamBendall
WilliamBendall
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 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
Ells
Ells
Right there with Babe
Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)

Group: General Forum Members
Points: 790 Visits: 931
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
Cool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search