asked on

Checking a file exists in DTS

Hi All,

Im wanting to use dts to check if a file exists and if it does execute a task else do nothing.

the file in question is c:\import\import.csv

Ive used the below taken from http://www.sqldts.com/211.aspx but i get a failure when run, am i missing something?

Cheers

Superblades

' Pkg 211 (File Exists - 2)
Option Explicit

Function Main()
Dim oFSO, sFileName

' Get the name of the file from the global variable "ImportFileName"
sFilename = DTSGlobalVariables("c:\import\import.csv").Value

Set oFSO = CreateObject("Scripting.FileSystemObject")

' Check for file and return appropriate result
If oFSO.FileExists(sFilename) Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If

Set oFSO = Nothing
End Function

Guy Hengel [angelIII / a3]

this line:
' Get the name of the file from the global variable "ImportFileName"
sFilename = DTSGlobalVariables("c:\import\import.csv").Value
should read:
' Get the name of the file from the global variable "ImportFileName"
sFilename = DTSGlobalVariables("ImportFileName").Value

superblades

ASKER

still doesnt work

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution.
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

superblades

ASKER

Maybe it would be clearer to explain what im trying to achieve,

a user will put a file in a folder c:\import every evening at 6pm, (import.csv)

at 6am i want dts to check if the file exists and if it does copy the contents from the csv file into the sql database if it doesnt exist do nothing -

Then remove the file once the sql database has been populated from the import.csv file

Ive been looking at active x scripts on the web to do the check but i dont understand activex.

Ive got the transformation task working but i need the active x script to do the check and then on success proceed with the transformation task.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

if the file is named import.csv every time, then you don't need to get it from the dynamic properties?

Guy Hengel [angelIII / a3]

ps: the dts runs on the server, so the file (import.csv) needs to be on the server also...

superblades

ASKER

Realised yesterday what i'd done, i was using it as a script not a workflow script,

changing that resolved the problem!

Thanks all

Superblades