January 16, 2006 at 8:18 am
Hi Gurus,
I have a txt file which needs to uploaded to a table.
The steps are :
1. Import txt file from another Server -- Done
2. Check file existence on the Server.
3. If the file exists, truncate the table in a database
4. Upload data from txt file to the table.
I am stuck up with step 2 where I need to check the File Existence.
The Import file is taken care by a Batch file, the truncate table and upload data is taken by a SQL job.
Please suggest how to check for file existence...
Thanks ..Kishore
January 16, 2006 at 9:01 am
Kishore,
You can use master..xp_fileexist. Problem with that is that is its return code. You might have to go for something like:
CREATE TABLE #fileexists ( exist int, dir int, parent int)
INSERT #fileexists
EXEC master..xp_fileexist @sourcefilepath
IF EXISTS(SELECT * FROM #fileexists WHERE exist = 1)
/* do some thing */
DROP TABLE #fileexists
RETURN 0
Jan
January 16, 2006 at 10:06 am
Thanks for the reply Jan. Its a good suggestion.
HOwever, I am still stuck up.
I need to run this in SQL job steps.
My 1st step would be as you suggested to use fileexist. It returns the value, and how do I trap this and pass it to the next step.
My 2nd step would be to truncate the table if the file exists and exit the job if the file does not exists.
please suggest.
Thanks--Kishore
January 17, 2006 at 2:23 am
Depending on what your 'Upload data from txt file to the table' process is you caould put the file exists test, truncate and load all in one step.
Or check the existance of the file and do a RAISERROR if it exists, then set the step 'On Success' to 'Quit job with success' and 'On Failure' 'Goto next step' where next step is the truncate step.
Far away is close at hand in the images of elsewhere.
Anon.
January 17, 2006 at 6:48 pm
Could you just collapse them into one step?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply