Look for a file and if it exits then run a package

Look for a file and if it exits then run a package

Post by Ather Mia » Tue, 10 Sep 2002 12:12:39



Hi,

I am developing a Datamart using DTS.  Which is near completion.  I need
some help and advise about an issue.  Our DBA puts the extract in a
directory and I import those into a SQL 2K db.  Depending on the data these
extracts get done at varying times. Anywhere between 10 or 20 minutes
difference from day to day.

The only way I know that these files are done is that he sends me a dummy
file every day which lets me know that the extracts are done.  Which would
be the best way in DTS to look for this file and if it exists move it to
different directory and then execute a dts package.

I am upgrading this application from Access and in Access I have a batch
file which looks for this dummy file it exits then it moves it to another
directory and after the move it fires off an Access macro which does the
data scrubbing and the rest.

Any advise or help would be greatly appreciated.

Thanks

Ather

 
 
 

Look for a file and if it exits then run a package

Post by John Bel » Tue, 10 Sep 2002 19:29:02


Hi

You could try something like the following:

Function Main()
        Dim oFSO, sFileName, sWorkingFileName

        sFilename = "C:\Holding\TestFile.txt"
        sWorkingFilename = "C:\Input\TestFile.txt"

        Set oFSO = CreateObject("Scripting.FileSystemObject")

        ' Check for file and return appropriate result
        If oFSO.FileExists(sFilename) Then
        If oFSO.Move(sWorkingFilename);  Then
                Main = DTSTaskExecResult_Success
        Else
                Main = DTSTaskExecResult_Failure
                End If
        Else
        Main = DTSTaskExecResult_Failure
        End If

        Set oFSO = Nothing
End Function

John



Quote:>Hi,

>I am developing a Datamart using DTS.  Which is near completion.  I need
>some help and advise about an issue.  Our DBA puts the extract in a
>directory and I import those into a SQL 2K db.  Depending on the data these
>extracts get done at varying times. Anywhere between 10 or 20 minutes
>difference from day to day.

>The only way I know that these files are done is that he sends me a dummy
>file every day which lets me know that the extracts are done.  Which would
>be the best way in DTS to look for this file and if it exists move it to
>different directory and then execute a dts package.

>I am upgrading this application from Access and in Access I have a batch
>file which looks for this dummy file it exits then it moves it to another
>directory and after the move it fires off an Access macro which does the
>data scrubbing and the rest.

>Any advise or help would be greatly appreciated.

>Thanks

>Ather


 
 
 

Look for a file and if it exits then run a package

Post by Allan Mitchel » Tue, 10 Sep 2002 22:11:35


By far the easiest way is to use the FileSystemObject (FSO)

In my example of Looping, Importing and Archiving at www.SQLDTS.com
I use the FSO and check that the number of files in a directory > 0
I also move each file using the FSO to a disfferent directory and in
addition to this if I find no files left in the original directory I
exit gracefully

Using these parts of this package and a SQL Server job you will be able
to do everything you need.

Looping, Importing and Archiving
http://www.sqldts.com/default.aspx?t=6&s=103&i=246&p=1&a=0

--

Allan Mitchell
MCSE, MCDBA
www.SQLDTS.com

 
 
 

Look for a file and if it exits then run a package

Post by Darren Gree » Wed, 11 Sep 2002 00:28:21


A similar method to those already posted, but uses workflow so the package
does not actually fail. May be useful-

How can I check if a file exists?
http://www.sqldts.com/default.aspx?6,103,211,0,1

--
Darren Green (SQL Server MVP)
http://www.sqldts.com


Quote:> Hi,

> I am developing a Datamart using DTS.  Which is near completion.  I need
> some help and advise about an issue.  Our DBA puts the extract in a
> directory and I import those into a SQL 2K db.  Depending on the data
these
> extracts get done at varying times. Anywhere between 10 or 20 minutes
> difference from day to day.

> The only way I know that these files are done is that he sends me a dummy
> file every day which lets me know that the extracts are done.  Which would
> be the best way in DTS to look for this file and if it exists move it to
> different directory and then execute a dts package.

> I am upgrading this application from Access and in Access I have a batch
> file which looks for this dummy file it exits then it moves it to another
> directory and after the move it fires off an Access macro which does the
> data scrubbing and the rest.

> Any advise or help would be greatly appreciated.

> Thanks

> Ather

 
 
 

Look for a file and if it exits then run a package

Post by Ather Mi » Thu, 12 Sep 2002 05:23:53


Thank you very much for all your suggestions. I will experiment with
all the suggestioin and see which is better suited for application.

Sincerely,

Ather


> A similar method to those already posted, but uses workflow so the package
> does not actually fail. May be useful-

> How can I check if a file exists?
> http://www.sqldts.com/default.aspx?6,103,211,0,1

> --
> Darren Green (SQL Server MVP)
> http://www.sqldts.com



> > Hi,

> > I am developing a Datamart using DTS.  Which is near completion.  I need
> > some help and advise about an issue.  Our DBA puts the extract in a
> > directory and I import those into a SQL 2K db.  Depending on the data
>  these
> > extracts get done at varying times. Anywhere between 10 or 20 minutes
> > difference from day to day.

> > The only way I know that these files are done is that he sends me a dummy
> > file every day which lets me know that the extracts are done.  Which would
> > be the best way in DTS to look for this file and if it exists move it to
> > different directory and then execute a dts package.

> > I am upgrading this application from Access and in Access I have a batch
> > file which looks for this dummy file it exits then it moves it to another
> > directory and after the move it fires off an Access macro which does the
> > data scrubbing and the rest.

> > Any advise or help would be greatly appreciated.

> > Thanks

> > Ather

 
 
 

1. Package run correctly in dts designer, but run error when schedual this package

I design a package in which there has a function that copy
a local file to net map driver. This package running
correctly in dts designer, but when I schedule this
package in sql server agent , this package running error.

the vb activex script:

Function Main()
      DIM FSO
       set FSO=createobject("scripting.filesystemobject")
        FSO.CopyFile "c:\autoexec.bat","g:\autoexec.bat"
        Main = DTSTaskExecResult_Success
End Function
/*  the g: driver is mapped from another host.

the error :  Error = -2147220482 (800403FE)

why this happen?

2. Accessing .SDF files on W2K desktop

3. DTS Packages that run batch files in scheduler

4. Apache JServ / Servlets - Unable to load Msql JDBC library

5. Run DTS packages from batch file from AS400

6. SQL Backup to NAS

7. Passing run-time params to a File DTS Package

8. dBASE III + HELP!!

9. Running DOS batch files in DTS packages

10. Exit Package

11. Errors Running Package within Package..

12. Running a DTS Package from A DTS package

13. DTS Hangs when running a package of packages