Solved

Checking a file exists in DTS

Posted on 2007-03-27
7
544 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 142

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 142

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Some sers suddenly getting error popup msg 28 89
How to search for strings inside db views 4 28
SQL Server Error 21 8 25
Help to build a Proc... 6 23
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

831 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