Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-09-23 : 09:37:04
|
| Harry writes "I basically have an Excel file that is being updated and added to a specific folder on the network. I compare the date of the file to the date of the table in the SQL database. If the file date is newer than the table date ... I run the DTS which extracts a row from the Excel file ... drops, re-creates, and then populates the table in the database. The SQL Agent job would be set to run once a day to check the file in the folder to see if it had been updated. That way, anytime the file is updated ... the database table would be updated as well.I set the jobe to "Quit upon success" in SQL Agent. I parses fine as well.I have got the code below to work fine when executing it via an ".asp" page. When I add it to the script in a SQL Agent Job and attempt to run it ... it doesn't work.Any clarification/ideas???Thank you in advance...------------------------------------------------------------' Get the Date from the Excel File here....Dim fs, f, ExcelFileDateSet fs = CreateObject("Scripting.FileSystemObject")Set f=fs.GetFile("C:\ExcelFile.xls")ExcelFileDate = f.DateCreated' Connect to database and get Table creation date here.....Dim Myconn, rs, SQLTableDate, TableQuery, TableDateSet Myconn = CreateObject("ADODB.Connection")Set rs = CreateObject("ADODB.Recordset")Myconn.Open = "Driver = {SQL Server};Server = A_Server;dsn=A_Server_Connect;TrustedConnection = yes;"TableQuery = "SELECT 'TableDate' = crdate FROM sysobjects WHERE name = 'Departments'"rs.open TableQuery, Myconnset Flds = rs.Fieldsset SQLTableDate = Flds("TableDate")' run the DTS package if the Excel file is newer than the table..If ExcelFileDate > SQLTableDate Then Set oPkg = CreateObject("DTS.Package") oPkg.LoadFromSQLServer "A_Server_Connection", , , _ 256, , , , _ "DTS_Update_Department_Table" oPkg.Execute oPkg.Uninitialize() Set oPkg = NothingEnd If' Done ... clean up...'fs.closeset fs=nothing'Myconn.close'set Myconn = Nothingrs.closeset rs = Nothing" |
|
|
|
|
|
|
|
Scripting SQL Agent Jobs to run DTS