Post reply

DTS & Excel Files

  • October 12, 2005 at 12:02 am

    #91757

    Hello all

    I have upto 12 excel files being generated every day. Each file has 2 worksheets and the same number headers with only the number of records changing. Each file though is genereated with a different name ie cltest1.xls, cltest2.xls etc....

    I have created a DTS package which impports this data and have this linked to a JOB which run another 25 steps or so after the successful import.

    Is there a way to automatically pickup *.xls file for import from a nominated folder without having to manually select which file to import through the DTS job?

     

    cheers

  • October 12, 2005 at 2:26 am

    #596724

    I don't think it's as simple as just clicking a 'process all files in folder' check box - unfortunately.

    Here's an article that might be of use to you though - it seems to do roughly what you want, though you'll have to write some code to achieve it.

    http://www.databasejournal.com/features/mssql/article.php/1461661

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • October 13, 2005 at 7:20 am

    #597070

    The link that Phil posted is a good solution.

    An alternative is to use a DOS script that loops through the files and uses DTSRun with the filename assigned to a global variable through the command line. That gives you some flexibility in moving files around and wildcarding. It's especially good if you have DOS skills but don't have VB skills.

    This checks that the file size (-z) is not zero and then runs a DTS package:

    FOR %%I IN (Folder\*.XLS) DO ( IF %%~zI NEQ 0 DTSRun /S %SERVER% /E /N import /A File:8=%%I )

  • October 13, 2005 at 4:39 pm

    #597222

    thanks, i will try and let i know

     

    cheers

  • October 13, 2005 at 4:56 pm

    #597224

    Theo, I believe you can automated your DTS Job if the excel files have fixed name.

    1. write an activeX script to check if all files exists

    2. transfer data from excel to tables.

     

  • October 14, 2005 at 3:33 pm

    #597474

    You can create an ActiveX script step in DTS, and then use a FileSystemObject to get a list of files. I believe you need to call the "GetFolder" method, then go through the files in the Files property collection of the folder returned.

    I never messed with this before, but it is worth looking into.

    -- Stephen Cook

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply