June 17, 2008 at 7:32 am
Hi guys,
This is an "is this even possible?" question, which will hopefully save me a fair bit of time/work, so all input is appreciated.
My company's sales reps send in their individual weekly data on Excel sheets - there's maybe 8 or so data items on each sheet, and we receive 80 of these sheets each week. Someone then keys this data into our sales database (SQL Server 2005), via a little Access front end I made.
What the company would now like to do is automate some/all of this process. My question is this: can SSIS be used to grab values from set cells (ie. B4, C6, D1 - assuming all of our reps work from the same template) on the Excel sheets, and update the relevant records in our DB?
Again, thanks in advance.
Scott
June 17, 2008 at 7:56 am
Yes, but you would either be pulling in all cells and finding the cell you want based on it's row / column, or you would be using a script task and the Excel object model.
Since the script task would work best, you may be better off building this from the other direction and writing some VBA code in your workbook that inserts the data on a button click or something.
June 17, 2008 at 8:03 am
It is possible.
Michael's idea of building a VBA script in Excel and using that might be better, might not, it depends on how much data you're moving around and how easy it would be to get them to click the right button in Excel.
If you do go with the SSIS idea, it will be easy enough to set up, so long as the data is in a standardized format. Have the files go to a specific folder, have SSIS run a For Each Next loop on all the files to pull the relevant data into a table, then run a proc that does the updates. Very easy, so long as the Excel spreadsheets are standardized.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2008 at 8:10 am
GSquared (6/17/2008)
If you do go with the SSIS idea, it will be easy enough to set up, so long as the data is in a standardized format. Have the files go to a specific folder, have SSIS run a For Each Next loop on all the files to pull the relevant data into a table, then run a proc that does the updates. Very easy, so long as the Excel spreadsheets are standardized.
I'm almost certain the SSIS idea is how I want to go. The data will be in a standardized format, and I can definitely have the xls files go to a specific folder. What I'll probably do to simplify it a little is re-design the template the reps are using so that the data is all in one line on each sheet. Am I correct in thinking that SSIS can then pull that one line from each sheet, insert into a temporary table, and then I set off a proc to do the updating of my actual tables? Sorry if I seem a little dense, I haven't used SSIS before.
Thanks for helping,
Scott
June 17, 2008 at 8:30 am
It can definitely do what you're describing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2008 at 8:32 am
Yes, if you can get the data into either a single row or a single column in just one pre-determined sheet, SSIS will pretty easily allow you to connect to workbook and query the worksheet with the information.
It will also make it pretty easy to loop through a directory full of the workbooks and deal with each of them.
June 17, 2008 at 9:37 am
Out of curiosity, does DTS also allow for this, or is it something which has to happen through SSIS?
June 17, 2008 at 2:23 pm
It should be easy enough to do in DTS. I haven't done that (I have in SSIS), but I'm sure it can be done. I have done similar things in DTS.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2008 at 2:27 pm
I've done something like that with DTS a few times. For better or worse - I found it easier to have DTS handle the importing of a single file, and something external (like a .net console) handle the looping (so the console app finds the files you need to call the DTS on, and performs multiple calls to the DTS package once per file it finds).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 17, 2008 at 2:29 pm
Matt Miller (6/17/2008)
I've done something like that with DTS a few times. For better or worse - I found it easier to have DTS handle the importing of a single file, and something external (like a .net console) handle the looping (so the console app finds the files you need to call the DTS on, and performs multiple calls to the DTS package once per file it finds).
Yeah, that was my experience too. SSIS made this kind of thing a whole lot better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2008 at 8:07 pm
OpenRowSet does this very nicely... and with a little help from our friend "Dirtree", you can make it "auto-magic". Lemme see if I can find my sample script for this...
--Jeff Moden
June 17, 2008 at 8:56 pm
Found 'em...
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Temp\OpenRowSetTest2.xls;HDR=yes',
'SELECT * FROM [Sheet1$a4:c]')
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Temp\OpenRowSetTest2.xls;HDR=Yes',
'SELECT LastName,FirstName FROM [Sheet1$a4:c]')
... and I've attached the example spreadsheet I used in the code above...
--Jeff Moden
June 17, 2008 at 9:02 pm
Oh yeah... I forgot the "other part"... Dirtree...
Lots of people know that Dirtree can take 2 parameters... one for Directory and 1 for how many levels deep to go... Very few know about the 3rd parameter which turns this nearly useless command into something that really helps figure out which files to import...
... try this and see...
EXEC Master.dbo.xp_DirTree 'C:\Temp',1,1
Notice the 3rd column that shows up? Do you notice that the "SubDirectory" column now has file names in it? If you were to do an INSERT/EXEC using this command, do you suppose it would help you step through the 80 spreadsheet names to be imported using OpenRowset? 😉
--Jeff Moden
June 18, 2008 at 5:16 am
Thanks for all the assistance with this, guys. If anyone is in a similar situation, this link provides a nice step-by-step walkthrough on how to set up an SSIS package to do this:
http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html
Even I managed to get it working, and I'm a donkey.
June 18, 2008 at 7:13 am
GSquared (6/17/2008)
Matt Miller (6/17/2008)
I've done something like that with DTS a few times. For better or worse - I found it easier to have DTS handle the importing of a single file, and something external (like a .net console) handle the looping (so the console app finds the files you need to call the DTS on, and performs multiple calls to the DTS package once per file it finds).Yeah, that was my experience too. SSIS made this kind of thing a whole lot better.
You guys should team up and write an article on it...
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply