Solved

Checking a file exists in DTS

Posted on 2007-03-27
7
537 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]
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
>>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]
Comment Utility
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]
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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 …
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now