Automated import of XLS to SQL

I have a customer who insists on emailing an excel spreadsheet to me to be uploaded to a sql database. I came across a script at one point that laid out how I would script this out. Basically it was in parts, 1. open email program and get attachment. 2.After saving attachment closing email program in order to be used the next day or next time an attachment was sent. 3. import the attached spreadsheet into SQL database. I would also like to be able to schedule this to run with the built in Windows 2000 scheduler.

Can anyone help me out on this one??

Thanks,
WillWhat email application are you using ... Also, you want to do all these steps within sql as a job ?|||Currently I have it set up with the pre-security patched version of Outlook 2k. I would love to have it set up as one job that launches each morning at 8am. Currently I have a script that I run manually that opens Outlook, saves the attachment, moves the message to a subfolder, sends a confirmation email then closes Outlook. It is a wsf file (see below) but when I try to run it as a scheduled task it doesn't do a darn thing.

Code:
<job>
<script language="VBScript">
dim WshShell
Set WshShell = WScript.CreateObject("WScript.Shell")
Set objOL = WScript.CreateObject ("Outlook.Application")
Set MAPI = objOL.GetNamespace("MAPI")
Set myStore = MAPI.Folders("Mailbox - Tunisia Information")
Set Folder = myStore.Folders("Inbox")
' Open up the Processed Folder under the inbox
Set DestFldr = Folder.Folders("Processed")
For Each myItem In Folder.Items
For Each att In myItem.Attachments
att.SaveAsFile "\\naslc03\data\TunisiaProdData\" & att.Filename
Next
' Move the Mail to DestFldr
myItem.Move DestFldr
Next

' Create mail message.
Set olMailMessage = objOl.CreateItem(olMailItem)
With olMailMessage
Set olRecipient = .Recipients.Add("TunisiaProdData@.pioneernrc.com")
.Subject = "Tunisia Attachment"
.Body = "The Tunisia email message was created was processed" _
& " and the attachment was successfully saved to the S: Drive."
.Send
End With
Set olMailMessage = Nothing

objOL.quit

</script>
</job>|||Good - You can try to implement this within dts, using the activex script task. Once this task is complete, you can import the excel file. The easiest way to set this up if you have not worked with dts before, is to go to Enterprise Manager -> Databases -> The database you want to import into -> tables. Right click on tables -> All tasks -> Import Data. Follow the instructions - making sure at the end to save it. When you have saved it, you can modify the package under Data Transformation Services -> Local Packages. Open your package in design mode and create an activex script task. Insert your code into that task and run that step to see if it will function properly.|||Thanks I will give that a shot and let you know.|||If adding it as an activex script does not work, you can still keep the rest of the package (the excel import). When you create a job based on this package, you can add a step that will execute an operating system command (which would be your script).

Auto Updates for SQL Server?

Our IT guy insists that he must be present anytime the SQL Server automatic updates from Microsoft are installed on the server or the server will crash. What has he been tinkering with to cause this to happen or is he just giving us a line?

Well, I wouldn′t apply all patches to SQL Server without any notice ot testing. Normally the updates or patches don′t break the functionality of SQL Server. To be sure, I would test the patch on a testing server. If you have a WUS, you can setup the server to confirm the patch first before applying the patch on the SQL Server.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||I guess I needed to be a little more specific. He is referring to the auto updates which come from Microsoft (just like on a personal PC). He evidently has the server set up so that the updates sit there until he decides to install them (like the option on a personal PC) which isn't really the problem but then he has turned around and is saying that when he does install the updates (rather than anyone with knowledge to do so) he absolutely has to be there or the server will crash. What could he have possibly done which would make the server crash if he doesn't personally oversee the update process?|||

hi,

this sounds kind "confusing"...

does he perform particular steps in order to avoid "system crashes"?

regards

|||I don't have any idea. We're assuming he's just feeding us a line but he has an unusual family history (which has us particularly concerned at the moment). Believe me this is not a joke. We are not a Dept. of Defense facility or anything like that but we are just a small business where the owner hired him because of his computer knowledge (but does know his background). Now the owner is concerned this is coming back to bite him if we can't figure out why he needs to be there when any updates are installed. We are very worried that he has planted some type of code to bring the business to its knees. Other than a disaster recovery plan which I am already looking into, what other ideas would anyone suggest. I don't want to get anymore specific than what I have since I don't know whether he reads these forums or not.|||

I'm not sure this is a productive discussion for this forum. No one here could (or should) offer any suggestiona as to what your IT guy might or might not do to a computer to make it crash. If you have significant concerns about one of your employees, you need to take it up with your boss. If you feel it is a real possibility that your IT guy might do something maliceous to your server, you should have a qualified computer technician take a look at it.

Regards,

Mike Wachal
SQL Express team

FWIW: There are no patches for SQL Express available through Microsoft Update at this point, you have to download and apply them manually.