Post reply

Transforming multiple text files in DTS package

  • March 29, 2005 at 10:31 am

    #61541

    I have a directory of text files that are being transformed by a DTS package that I kick off manually within SQL Server 2000.  The package was created using SQL Server 2000 on the server itself using Terminal Services. 

    All of the files names are consistent, as is the delimited data.  The package works fine for the first iteration, but when it goes to pick up the second file, I get this error message:  "Error opening datafile:  The system cannot find the file specified."

    I'm using an ActiveX script to grab the next file name.  The previously-loaded file is moved using FSO to another directory prior to the next file load.  I output to the screen for debugging purposes, and the next file name and full path is correct.  Here's what I've tried to alleiviate the issue:

    1)  The mssqlserver and sqlagent services are running under an account in the Administrator group

    2)  The DTS package owner is the same user that runs the mssql services

    3)  I applied a package owner and user password (do they need to be the same as the user password?)

    4)  I'm using full pathing to the directory and files

    5)  I made sure that the user has full control rights to the directory that contains the file and that the files are not read-only

    If I run the package again after the error, the next file gets loaded fine, but errors out again on the subsequent file.

    Obviously, I'd like to automate this process, but can't until I get this issue resolved.  Any other ideas?

    Thanks!

    Keith

  • March 29, 2005 at 1:24 pm

    #549399

    This example has helped me in the past:

    http://www.sqldts.com/default.aspx?246



    Michelle

  • March 30, 2005 at 8:15 am

    #549550

    Hi,

    I'm making guesses....are you maybe zooming through all the files and processing only the last one? You might think you are on the first one but have ended up at the end and when you process that one; there ain't no more.

    Probably not related to your problem but something that took me a long time to find: Right click on your transformation, Click on Options, Check that box that says "Close connection on completion". That little box caused me grief for several days. I kept getting intermittent 'file open' or 'file not closed' errors.

    Teague

     

     

  • March 30, 2005 at 12:19 pm

    #549620

    Thanks, Teague.  That's exactly what the problem was.

Viewing 4 posts - 1 through 3 (of 3 total)

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