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).

automate rendering an rdl into xls and upload to AS400 on diff ser

I would like to automate rendering an rdl into an excel spreadsheet, and then
upload the excel spreadsheet to our As400. The Report Server and As400 are
on different servers and I have encountered communication issues between the
2 because we have the AS400 on a Novell network.
I have tried establishing a subscription which successfully renders the xls
to the report server. However, Client Access forces me to login and
authenticate before I can execute the file transfer back to the As400. I
was hoping there might be a way to write some sort of process that would
automatically do this for me.
My pc knowledge is limited.
Any help or alternate suggestions would be appreciated.
PBOn May 16, 2:52 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I would like to automate rendering an rdl into an excel spreadsheet, and then
> upload the excel spreadsheet to our As400. The Report Server and As400 are
> on different servers and I have encountered communication issues between the
> 2 because we have the AS400 on a Novell network.
> I have tried establishing a subscription which successfully renders the xls
> to the report server. However, Client Access forces me to login and
> authenticate before I can execute the file transfer back to the As400. I
> was hoping there might be a way to write some sort of process that would
> automatically do this for me.
> My pc knowledge is limited.
> Any help or alternate suggestions would be appreciated.
> PB
You might want to look into the robocopy utility (for copying the
resulting Excel file to the AS400 machine). I'm not sure if it will
access the AS400 machine, but its worth a shot. Hopefully this is
helpful.
Regards,
Enrique Martinez
Sr. Software Consultant|||>>However, Client Access forces me to login and
>> authenticate before I can execute the file transfer back to the As400
>> I was hoping there might be a way to write some sort of process that
>> would
>> automatically do this for me. ..
Client Access does have some COM client-based tools as well as an EXE that
can be scripted with a command-line instruction file -- so it's possible.
You are embedding the credentials in the script or command-line text file,
when you do it this way.
I will log onto a client with Client Access loaded and get back to this
thread later today with more detail, if I can. But here is an alternative
idea or two:
* -- One of my clients had exactly your setup (including the Novell part)
and there was a similar requirement. The way they had it working was to
have the MS-component put the file someplace shared and an AS400 batch job
picked up the file from there. I don't know if it was the best way, or the
only way, but it worked. (We did also use the command-line text file
approach for other things, but it was before the COM approach was
available.)
* -- You could use an AS400 facility that can consume web content and pull
the report back, in Excel format, rather than pushing with the RS
subscription. I would use URL access rather than SOAP/web service for this.
There is a $400 tool called GETURI by Brad Stone to do it --
http://www.bvstools.com/geturi.html -- which makes me think that there is
no native AS400 facility to make an HTTP call' I would have expected there
was one -- But you would know this better than I do.
* -- If AS400 has a native FTP-GET facility, you could have the RS
subscription publish to an FTP directory, and have the AS400 job pick it up
from there.
>L<
"ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
news:1AB759AF-0D15-4A63-9375-2647E62B40B1@.microsoft.com...
>I would like to automate rendering an rdl into an excel spreadsheet, and
>then
> upload the excel spreadsheet to our As400. The Report Server and As400
> are
> on different servers and I have encountered communication issues between
> the
> 2 because we have the AS400 on a Novell network.
> I have tried establishing a subscription which successfully renders the
> xls
> to the report server. However, Client Access forces me to login and
> authenticate before I can execute the file transfer back to the As400. I
> was hoping there might be a way to write some sort of process that would
> automatically do this for me.
> My pc knowledge is limited.
> Any help or alternate suggestions would be appreciated.
> PB|||Here is the additional information: The COM client library that is
associated with Client Access is a DLL with this name (location on my box):
c:\program_files\ibm\client_access\shared\cwbx.dll
... I'm telling you this in case you want to look at it using an object
browser in Visual Studio or whatever.
You will see an object called AS400System in this library, and you will also
find an object called DatabaseUploadRequest.
On the AS400System object, you set up your user id and password, and on the
DatabaseUploadRequest object you tell it what file (on the PC file system)
you want to upload into the AS400.
You will find some sample code doing this in this thread:
http://www.systeminetwork.com/isnetforums/showthread.php?t=33403
So, you could have a little VBS script that looks like the code you see in
that thread. You set up a Scheduled Task Windows Task Manager, at some time
*after* the subscription publishes the file to the PC location or use any
other utility you like that runs timed tasks (like a cron job in *nix) --
but Task Manager is easy and free <g>.
Remember that you want to run this VBS script on the box that has Client
Access installed, not necessarily the RS Server, so basically you can set up
the task on any such client box that can "see" the location to which RS
publishes the report. If you need help, holler.
BUT I still say it would make more sense to "pull" the file from the AS400
side!!
>L<
"ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
news:1AB759AF-0D15-4A63-9375-2647E62B40B1@.microsoft.com...
>I would like to automate rendering an rdl into an excel spreadsheet, and
>then
> upload the excel spreadsheet to our As400. The Report Server and As400
> are
> on different servers and I have encountered communication issues between
> the
> 2 because we have the AS400 on a Novell network.
> I have tried establishing a subscription which successfully renders the
> xls
> to the report server. However, Client Access forces me to login and
> authenticate before I can execute the file transfer back to the As400. I
> was hoping there might be a way to write some sort of process that would
> automatically do this for me.
> My pc knowledge is limited.
> Any help or alternate suggestions would be appreciated.
> PB

Autoimport from xls to SQL 2000?

I have a program setup the exports check request data into a xls file every
week on a shared directory on Windows 2003 server. I'd like a way to import
that data into SQL 2000 on a weekly basis. (Shared directory and SQL on same
server).
Ideas?
just create a dts job to do it.
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:42DDB5A3-0F75-4ABE-B7D5-CBA86251AC6C@.microsoft.com...
>I have a program setup the exports check request data into a xls file every
> week on a shared directory on Windows 2003 server. I'd like a way to
> import
> that data into SQL 2000 on a weekly basis. (Shared directory and SQL on
> same
> server).
> Ideas?
>