[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 438
  • Last Modified:

SQL 2005 Integrated Services - DTS Package that only runs if source file has been modified

I have a package that imports data from a comma delimited text file into an SQL 2005 table every 10 minutes.  Currently, I have it set to every 10 minutes throughout the day because I need the data to be up to date and I don't know when the souce file is FTPd (It gets FTPd every few minutes sometimes but not at all hours of the day).  Is it possible to have the package only import and run if the source file has been modified (ie. FTP'd recently or content changed).  I believe that this type of functionality does exist for XML files, but not sure about comma delimited or how I would go abouts checking for this.  Thanks!  Any precise instructions would be greatly appreciated.
2 Solutions
You could use xp_cmdshell to do an dos dir command. If you do an

--create a temp table

insert #tmp execute master..xp_cmdshell "dir ......"

you can get the results into a temp table, and check the modified date.

If possible, after the ftp process runs, get it to send a flag file, and check for that (so ftp csv, ftp flag.txt_. When flag.txt turns up in the directory, you know you need to process the csv file (deleting the flag file afterwards). This will work if there is a reasonable time gap between updates to the csv.
I've been messing about in SSIS. Here is my theory:

1. FTP the file to a holding folder
2. Use XCOPY /D to only copy it to a new folder if it is newer (assuming the data is preserved after the FTP occurs)
3. The result of step 2 will be 0 files copied or 1 files copied.
4. Its possibly that you can pick up this result from an SSIS execute process task... but I can't actually get it to work.

I thought there was a command in FTP that allowed you to only copy newer files? You could pipe the output of that to a file, then check that output in SSIS and act accordingly.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now