Check for a file before executing DTS

Thanks in advance for any help offered!

I am having a text file sent from another location on a daily basis. Suppposely @. 2:00 am. I created a DTS to bring the data into a local table (5:00AM). The data I am bringing in replaces the old data. So what happens in the DTS package is all data is dropped from the table and then the new data is inserted.

I then have another job run that runs later in the day before the next incoming data arrives (7:00PM). This job deletes the old text file so that it is not appended the next time the text file is sent to me.

The problem is that for the last two days, the server sending the text file did not send the files before my local jobs run (up to 9:00AM and 7:00AM). Thus my 7:00PM job has deleted the old text file. Then later my job that calls the DTS runs (5:00AM); it then drops all data and then tries to load new data that is not there because my 7:00PM job deleted the text file and the their 2:00AM has not delevered the new text file.

My question is; what is the best way to script a job that checks to see if the text file exist before dropping the existing table?

I know that the whole process could be handled better if both the supplier of the data and me the end user could be more flexible. The problem is that the sender is going to send the data in the manner (time and method) that requires the least amount of work for them. I just need to deal with it.
Thanks,
LeeWhat type of text file ? How are you processing it now in the your dts script ? What steps are currently being used in your dts script ?|||hi

I had the same problem with a daily based data import DTS I created. Here's the way I solved it:

Open your DTS in design view. Add an ActiveX component, written in vb script:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
Set MyFile = CreateObject("Scripting.FileSystemObject")

If MyFile.FileExists(local_server_path) Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End Function
___________________________________________
___________________________________________

'local_server_path' is your file path on the server.

Use the 'onSuccess' event to start the rest of your DTS.

Hope it helps.

Check current database

I was wondering if it was possible to check what server database you are currently connected to, using T-SQL, when executing commands in Query Analyzer. Even though you choose the server and database when connection, sometimes by habit you may connect to the wrong server & DB, and execute an .SQL file. Is it possible to put a line of T-SQL at the beginning that performs this pseudo-code, as a safety feature:

-- While in Query Analyzer with a certain .SQL file open:
If current server <> 'TheCorrectServer' and current DB <> 'TheCorrectDB' then cancel this .SQL file execution.Hi there

In Query analyzer type the following to get server name

select @.@.servername

Unsure how to get database name, you could just always put;

use <databasename>

in your code, that way you would always be on the right database|||SELECT db_name()

returns the currently active database|||Thanks a lot! That will help.