February 25, 2005 at 6:27 am
Hi
I want to create a trigger that loads an excel file into a sql table whenever an users saves an excel in that directory.
How can we do this ?
Thx
El Jefe
JV
February 25, 2005 at 7:15 am
This depends on how the file gets there
If you are loading via ftp/asp for instance, your asp page can check for a correct load and then execute a corresponding DTS file
Second option, if you are simply allowing users to save to directory on server is to write a sweep routine in wsh/vbs. This can run every x mins and check for a file. if it finds one, it executes the load routine and then moves the file out of the directory to avoid reprocessing.
Hope this helps
Ross
March 2, 2005 at 5:49 am
A trigger would not be the right terminology or method of doign this. Seems to me the best solution here is a DTS package that when completed, gets scheduled as a job that runs every X minutes.
One, borrowing on what RossC said, make sure that you have a way/place to move a file once it is processed to avoid reprocessing.
Lets assume your directory structure is like this
D:\uploads\ForProcessing
D:\uploads\Processed
Create a new DTS package
Add some a package level Global Variable and lets call it targetFile
Make a data import task that will import an excel file (assuming they will always use the same formatting
Name that task something easy to remember, and make it get the name of file it needs to import from the DTSpackage Global Variable object named targetFile
Add an ActiveX script task, and use something similiar to the following to set the global variable
Function Main()
Dim FSO
Dim oFiles
Dim iFile
Dim targetFile
Set FSO = CreateObject("Scripting.FileSystemObject")
Set OFiles = FSO.GetFolder("D:\Uploads\ForProcessing")
For Each iFile In OFiles.Files ' For each file that is in the target Directory
' Set the Value of the Global variable here
DTSGlobalVariables("targetFile").Value = iFile.ParentFolder & "\" & iFile.Name
'Look up some code that will execute your import task
' EXECUTE YOUR DATA IMPORT TASK HERE
' Move the targetFile to the other direcotry to avoid reprocessing
iFile.Move("D:\Uploads\ForProcessing\" & iFile.Name, "D:\Uploads\Processed\" & iFile.Name )
Next
Set OFiles = Nothing
Set FSO = Nothing
Main = DTSTaskExecResult_Success
End Function
Fill in the blanks where needed to suit your needs and you should be pretty good to go.
March 2, 2005 at 7:45 am
If you have access to .Net dev tools you can also take advantage of the FileSystemWatcher - this would allow you to perform your tasks as each file is saved/moved to the "load" directory.
Otherwise - I like (and have used) sellertools method with great results.
March 3, 2005 at 4:51 am
Having read sellertools response, I would concur.
The reason I am sweeping the directory is that we perform other tasks at os level as well
The check sellertools is using will mean that no unnecessary processing is undertaken, and has the further advantage of the total solution being in one location.
Regards
Ross
March 3, 2005 at 5:33 am
Guy
Thx for the responses. We have some software that can trigger files. It's called automation. Then you can start stored procedures.
We'll try something out 😉
JV
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply