Solved

Checking a file exists in DTS

Posted on 2007-03-27
7
547 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:superblades
  • 3
  • 3
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18798717
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

0
 
LVL 4

Author Comment

by:superblades
ID: 18799135
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
0
 
LVL 4

Author Comment

by:superblades
ID: 18799463
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.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 18801713
>>The task reported failure on execution.<<
>> if it does copy the contents from the csv file into the sql database if it doesnt exist do nothing<<

You do realize that the code you posted was to be used in the ActiveX Workflow Script and not the ActiveX Task Script, right?  If you don't know the difference than you may want to re-read the article you posted.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18801839
if the file is named import.csv every time, then you don't need to get it from the dynamic properties?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18801844
ps: the dts runs on the server, so the file (import.csv) needs to be on the server also...
0
 
LVL 4

Author Comment

by:superblades
ID: 18806584
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
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question