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

Posted on 2007-10-03
Last Modified: 2013-11-30
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.
Question by:adrian78
    LVL 14

    Accepted Solution

    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.
    LVL 30

    Assisted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    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 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.

    779 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

    16 Experts available now in Live!

    Get 1:1 Help Now