How to run a DTS when I want to? 1

[bigfoot]

I have a DTS package that unpacks my files, converts them to SQL server, and truncates/copies the data into the web databases.

Here's the thing: I only want to do this when I copy the MS Access file up to my web server from my main server. Somehow kick it off from VB6, or .NET.

If I schedule it, the I have to make sure the file is there before the scheduled job, and I can not be sure what time my job will complete on this end and copy the file.

Any takers?

 

[kaymc]

'---------------------------------------------------------------------------------------------------------
'VB Script to run a DTS job only when a file exists. This puppy will sit waiting in the background
'quite happily. I hacked it together from some pre-existing code pretty quickly, so it will likely need some debugging.
'
'NOTE: The DTSRun line is just an example (which will obviously NOT WORK on your system.)
'Make sure you run the DTSRunUI utility to generate the correct command line for your job.
'---------------------------------------------------------------------------------------------------------

status = "no"

Set FSO = CreateObject("Scripting.FileSystemObject&quot

while status = "no"
ReportFileStatus("C:\temp\Whatever.mdb&quot
wend

MsgBox ("Done!&quot

Function ReportFileStatus(filespec)
Dim fso
Set FSO = CreateObject("Scripting.FileSystemObject&quot
If (fso.FileExists(filespec)) Then
InstallApplication("DTSRun /S "+chr(34)+"MyServer"+chr(34)+" /N "+chr(34)+"MyDTSJob"+chr(34)+" /G "+chr(34)+"{My Random Hash Code}"+chr(34)+" /L "+chr(34)+"C:\Temp\MyErrors.txt"+chr(34)+" /W "+chr(34)+"-1"+chr(34)+" /E &quot
status = "yes"
Else
status = "no"
WScript.Sleep 300000 ' Fortunately sleep takes up very little processing time
End If
End Function


Function InstallApplication(CommandLine) '...Run sequense of events listed above in CommanLine

Set WshShell = WScript.CreateObject("WScript.Shell&quot
Return = WshShell.Run(CommandLine, 1, true)

End function

 

[jweber9]

I have this setup as a job that checks for a file every 15 minutes to see if a file exists on the server. If it exists then I execute another job.

Function Main()
Dim fso
Dim filetogo
dim oJobServer
dim oSQLServer
filetogo = "File name to be found"
Set fso = CreateObject("Scripting.FileSystemObject&quot

If (fso.FileExists(filetogo)) Then
fso.deletefile (filetogo)
Set oSQLServer = createobject("SQLDMO.SQLServer&quot
oSQLServer.LoginSecure = True
oSQLServer.Connect "Server Name"
Set oJobServer = oSQLServer.JobServer
If (oJobserver.Status = 1) Then
oJobServer.Jobs("Job Name&quot.Start
End If

oSQLServer.DisConnect
Set oSQLServer = nothing
set fso = nothing
set filetogo = nothing

end if
Main = DTSTaskExecResult_Success
End Function

You may not want to delete the file until processing is done.

 

[bigfoot]

jweber9: You get the star.

kaymc: Thanks for your program, but yours will run all the time, where jweber9's is scheduled.

Thank you to you both!

 

[bigfoot]

Just found out that the look for a file program will not work.
The file is 16 megs and takes about 15min to FTP upload. When it first shows up in the folder, the program tries to use it, and it crashes. Rats!!! I thought I had it.

h-e-l-p

 

[Chao]

I would like to import data from a text file into a temp table in SQL Server. However, the temp table has an identity column. How can I indicate that it should automatically generate an identity value for each imported record?

 

[Chao]

Sorry, I mean to post the quest not to reply.

 

[kaymc]

Bigfoot,

Don't use the .mdb as the "look for" file. Uploaded a one or two byte file immediately after your main file, and begin processing the other only after the second file appears.