+ Reply to Thread
Results 1 to 5 of 5

File specified in the connection was not valid

  1. File specified in the connection was not valid

    I have created a SSIS package in VS2005 that very loads a couple of CSV text
    files as Flat File Connections and pumps the data into a SQL2005 table. I am
    able to import the package into Integration Services as a Stored Package from
    the file system and Run the Package without any problems.

    However, when I set this package up as a step in SQL Server Agent Job, I get
    a weird error message that "The file name 'c:\path\file1.txt' specified in
    the connection was not valid."

    I have of course confirmed that the file and path exists and the DOS command
    "edit 'c:\path\file1.txt'" opens the file for edit. I have also checked that
    the file is not locked by any other user or process.

    I have also checked that the user name that SQL Server Agent job is running
    under (SQL01\SQLSRV) has full permissions to the directory 'c:\path\'. To
    confirm this I have set up a step in the job that 'Operating system
    (CmdExec)' command "copy 'c:\path\file1.txt' 'c:\path\file1.bak'", and the
    new file is created as expected.

    Any ideas what the problem here is?

    Cheers,

    Stephen



    Message
    Executed as user: SQL01\SQLSRV. ...rsion 9.00.3033.00 for 32-bit Copyright
    (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:18:29 PM
    Error: 2007-01-31 20:18:30.29 Code: 0xC001401E Source: MyPackage
    Connection manager "File1" Description: The file name "c:\path\file1.txt"
    specified in the connection was not valid. End Error Error: 2007-01-31
    20:18:30.29 Code: 0xC001401D Source: MyPackage Description:
    Connection "File1" failed validation. End Error Error: 2007-01-31
    20:18:30.29 Code: 0xC001401E Source: MyPackage Connection manager
    "File2" Description: The file name "c:\path\file2.txt" specified in the
    connection was not valid. End Error Error: 2007-01-31 20:18:30.29 Code:
    0xC001401D Source: MyPackage Description: Connection "File2" failed
    validation. End Error Error: 2007-01-31 20:1... The package execution
    fa... The step failed.




  2. RE: File specified in the connection was not valid

    Stephen:

    I have run into this as well. As a final check to see if it really is a
    permission issue, create a Credentials (under the Security Node) and use your
    domain admin account. Then create a Proxy (under SQL Agent node) that has
    SSIS Package Execution authority using that Admin Credentials. Set up your
    Agent job to use your new Proxy.

    My guess is that it will go through under elevated permissions.

    By the way, does the SQL Agent service account have membership in any of the
    MSDB roles for SSIS (db_dtsadmin, db_dtsltduser, db_dtsoperator)? Check BOL
    for their usage.

    HTH
    --
    Todd Chittenden

    [If this response was helpful, please indicate by clicking the appropriate
    answer at the bottom]


    "Stephen" wrote:

    > I have created a SSIS package in VS2005 that very loads a couple of CSV text
    > files as Flat File Connections and pumps the data into a SQL2005 table. I am
    > able to import the package into Integration Services as a Stored Package from
    > the file system and Run the Package without any problems.
    >
    > However, when I set this package up as a step in SQL Server Agent Job, I get
    > a weird error message that "The file name 'c:\path\file1.txt' specified in
    > the connection was not valid."
    >
    > I have of course confirmed that the file and path exists and the DOS command
    > "edit 'c:\path\file1.txt'" opens the file for edit. I have also checked that
    > the file is not locked by any other user or process.
    >
    > I have also checked that the user name that SQL Server Agent job is running
    > under (SQL01\SQLSRV) has full permissions to the directory 'c:\path\'. To
    > confirm this I have set up a step in the job that 'Operating system
    > (CmdExec)' command "copy 'c:\path\file1.txt' 'c:\path\file1.bak'", and the
    > new file is created as expected.
    >
    > Any ideas what the problem here is?
    >
    > Cheers,
    >
    > Stephen
    >
    >
    >
    > Message
    > Executed as user: SQL01\SQLSRV. ...rsion 9.00.3033.00 for 32-bit Copyright
    > (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:18:29 PM
    > Error: 2007-01-31 20:18:30.29 Code: 0xC001401E Source: MyPackage
    > Connection manager "File1" Description: The file name "c:\path\file1.txt"
    > specified in the connection was not valid. End Error Error: 2007-01-31
    > 20:18:30.29 Code: 0xC001401D Source: MyPackage Description:
    > Connection "File1" failed validation. End Error Error: 2007-01-31
    > 20:18:30.29 Code: 0xC001401E Source: MyPackage Connection manager
    > "File2" Description: The file name "c:\path\file2.txt" specified in the
    > connection was not valid. End Error Error: 2007-01-31 20:18:30.29 Code:
    > 0xC001401D Source: MyPackage Description: Connection "File2" failed
    > validation. End Error Error: 2007-01-31 20:1... The package execution
    > fa... The step failed.
    >

    >
    >


  3. RE: File specified in the connection was not valid

    Hi Todd,
    Your suggestion was of great help, it has brought out of the error[Code:
    0xC001401D] which I had encoutered.

    Thanks a Lot,
    Godspeed...

    "Todd C" wrote:

    > Stephen:
    >
    > I have run into this as well. As a final check to see if it really is a
    > permission issue, create a Credentials (under the Security Node) and use your
    > domain admin account. Then create a Proxy (under SQL Agent node) that has
    > SSIS Package Execution authority using that Admin Credentials. Set up your
    > Agent job to use your new Proxy.
    >
    > My guess is that it will go through under elevated permissions.
    >
    > By the way, does the SQL Agent service account have membership in any of the
    > MSDB roles for SSIS (db_dtsadmin, db_dtsltduser, db_dtsoperator)? Check BOL
    > for their usage.
    >
    > HTH
    > --
    > Todd Chittenden
    >
    > [If this response was helpful, please indicate by clicking the appropriate
    > answer at the bottom]
    >
    >
    > "Stephen" wrote:
    >
    > > I have created a SSIS package in VS2005 that very loads a couple of CSV text
    > > files as Flat File Connections and pumps the data into a SQL2005 table. I am
    > > able to import the package into Integration Services as a Stored Package from
    > > the file system and Run the Package without any problems.
    > >
    > > However, when I set this package up as a step in SQL Server Agent Job, I get
    > > a weird error message that "The file name 'c:\path\file1.txt' specified in
    > > the connection was not valid."
    > >
    > > I have of course confirmed that the file and path exists and the DOS command
    > > "edit 'c:\path\file1.txt'" opens the file for edit. I have also checked that
    > > the file is not locked by any other user or process.
    > >
    > > I have also checked that the user name that SQL Server Agent job is running
    > > under (SQL01\SQLSRV) has full permissions to the directory 'c:\path\'. To
    > > confirm this I have set up a step in the job that 'Operating system
    > > (CmdExec)' command "copy 'c:\path\file1.txt' 'c:\path\file1.bak'", and the
    > > new file is created as expected.
    > >
    > > Any ideas what the problem here is?
    > >
    > > Cheers,
    > >
    > > Stephen
    > >
    > >
    > >
    > > Message
    > > Executed as user: SQL01\SQLSRV. ...rsion 9.00.3033.00 for 32-bit Copyright
    > > (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:18:29 PM
    > > Error: 2007-01-31 20:18:30.29 Code: 0xC001401E Source: MyPackage
    > > Connection manager "File1" Description: The file name "c:\path\file1.txt"
    > > specified in the connection was not valid. End Error Error: 2007-01-31
    > > 20:18:30.29 Code: 0xC001401D Source: MyPackage Description:
    > > Connection "File1" failed validation. End Error Error: 2007-01-31
    > > 20:18:30.29 Code: 0xC001401E Source: MyPackage Connection manager
    > > "File2" Description: The file name "c:\path\file2.txt" specified in the
    > > connection was not valid. End Error Error: 2007-01-31 20:18:30.29 Code:
    > > 0xC001401D Source: MyPackage Description: Connection "File2" failed
    > > validation. End Error Error: 2007-01-31 20:1... The package execution
    > > fa... The step failed.
    > >

    > >
    > >


  4. RE: File specified in the connection was not valid

    Glad to be of service.
    :-)
    --
    Todd Chittenden



  5. Re: File specified in the connection was not valid

    Hiyaaaaa
    After struggling for almost 14 hrs I got the way out for this kinda problem :)

    Few things that I did are mentioned in this blog
    (Access in msdb dbase, rights for the user executing job

    Now the solution that might be helpful (Its very silly but only until u figure it out)

    If u face such problem , create a folder locally and try giving path
    \\machine name \foldername

    see if it works or not

    Then create a folder on remote location just a folder not sub folder
    \\server454\folder

    and then check the access.

    Now my problem, I was having a sub folder in the folder which I was accessing,

    \\server454\folder\subfolder

    Although I added all the required rights in the sub folder too, but still it was not working until I figured it out that there were some special groups related to that server which required access to that sub folder tooo
    example 3 in my case (System, user and Creator Owner)
    and that too not only in Sharing but also in Security.

    And that was the final death blow to my problem


    Note all that works totally fine when u r executing it with BI but sucks a big time when you do that by deploying it in SQL server

    Time for a Beer !:)

    Cheers

    Bharat Sahani

    SSIS-BizTalk-SSIS

+ Reply to Thread