?
Solved

Checking a file exists in DTS

Posted on 2007-03-27
7
Medium Priority
?
563 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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

593 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