| Author |
Topic |
|
sqllearner
Aged Yak Warrior
639 Posts |
 Posted - 2004-10-05 : 12:57:12
|
| My task is to check a directory where a lot of files of different format will be saved .it could be pdf,word,excel..etc.The naming would be emp_id+_+abbreviation.ext.eg would be 52729_APS.pdf and I have to save the emp_id,abbrivation,path and extension in emp_details table for uploading at later stage.I had a vb program which would check and save it to the table.Now the I have to schedule a program which would check every 5 minutes in the directory to see whether any new documents are there and if present then I will have to save the details to the emp_details table Is there any way we can do this with DTS Or is there any way I can schedule that VB project to check every 5 minutes |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-05 : 13:21:10
|
| You just need to setup a job inside SQL Server Agent that calls this DTS package every 5 minutes. You would use dtsrun.exe to call the DTS package inside a job step. The job schedule would be every 5 minutes.Tara |
 |
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2004-10-05 : 13:49:01
|
| Hi Tara,But right now its a Vb program.how can I convert this to a DTS package which does this populating of information in the temp table |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-05 : 13:50:04
|
| You can't convert it. You'll have to manually create a DTS package.But why not just call your VB program from within SQL Server Agent as a job?Tara |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2004-10-05 : 16:41:50
|
| how can I call it in the sql job.In there I could find general,steps,schedules and In steps there is a new job specified what should I select to invoke the VB program |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-05 : 16:46:21
|
| In the General tab of the Job Step, take a look at the Type drop down list. You can call an executable via the Operating System Command (CmdExec) job step type.Tara |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2004-10-05 : 16:55:41
|
| But right now the vb.exe is in my local drive and its SWE.exe and I tried giving the path but still wasn't working |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-05 : 16:59:46
|
| The executable would need to be installed on the database server in order to call it from SQL Server. Otherwise, create a DTS package that mimics what your VB program is doing.Tara |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2004-10-05 : 18:07:40
|
| HI Tara,Will I be able to pick the files from the shared drive and meet the requirement I have specified in the first part |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-05 : 18:12:50
|
| That depends on permissions. Does the account that the SQL services uses have permissions to this shared drive/share? If it does, then yes you'll be able to do that.Tara |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2004-10-05 : 21:50:06
|
| Yes,the way I checked is..went to the dts package and tried to use a Excel sheet which can be saved in to the shared directory..IS that the permission u were talking about Tara |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2004-10-06 : 07:15:13
|
| You'd have to run the package on the server to see if the Services account has the requisite permissions (easiest to just schedule the package and try running the resultant job).Mark |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-06 : 12:26:40
|
| The permissions that I was talking about was for the account that the SQL services use. Open up Control Panel..Admin Tools..Services..Double click on MSSQLSERVER, go to logon tab. What account are you using? How about for SQLSERVERAGENT service?Tara |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2004-10-07 : 04:24:27
|
| Hi Tara,how can I call a stored procedure from s vb script..because from DTS iam writing a script say Hi213433/tytty.xls.Now the first part is the emp_id and the second part is the desc and the third is the extension.Now using split I could split them.Now I need to store them in table in emp_table of EM database .This Xls file is in a shared drive..I was able to get the split and all working but calling stored procedure to store the values in the table was giving problem..i was trying with execute usp_emp_splitter @filename in vbscript. |
|
|
|
|
issue regarding scheduling a Job