Solved

Checking a file exists in DTS

Posted on 2007-03-27
7
556 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

626 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