none
Cannot open data file error while running SSIS package through SQL Server job

    Question

  •  

    Hi,

    I created a SSIS package which will generate an output file and place it on a remote fileshare location which will look something like this

     

    \\RemoteServerName\RemoteFilePath

     

    The package is executing fine when I am executing it through BIDS or through execute package utility and writing the output file to remote file share location.

     

    I created a SQL job for the package and ran the Job. Then, its throwing an error saying

     

    Executed as user: Domain\User. Microsoft (R) SQL Server Execute Package Utility  Version 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  10:33:06 AM  Error: 2008-03-10 10:33:22.22     Code: 0xC020200E     Source: DFT_Generate Output File Description: Cannot open the datafile "

    \\RemoteServerName\RemoteFilePath\OutputFileName.txt".  End Error  Error: 2008-03-10 10:33:22.34     Code: 0xC004701A     Source: DFT_Generate Output File DTS.Pipeline     Description: component "FF_DST_Output" (160) failed the pre-execute phase and returned error code 0xC020200E.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  10:33:06 AM  Finished: 10:33:22 AM  Elapsed:  15.891 seconds.  The package execution failed.  The step failed.

     

    Domain\User have all the permissions on the remote file share location.

    SQL server agent is running with the log-on account Domain\User(same as the above).

     

    Could anyone help me in resolving this issue.

     

    Thanks & Regards,

    Sriram.

     

    Monday, March 10, 2008 10:26 AM

Answers

  • 1. are you using a proxy account with creds of a domain user?

     

    if not please set this up ( a number of threads cover this)

     

    If so

    does remotefilepath have an adminshare (c$)

    if so create a new share CDrive for proxy account

     

    Monday, March 10, 2008 10:33 AM

All replies

  • 1. are you using a proxy account with creds of a domain user?

     

    if not please set this up ( a number of threads cover this)

     

    If so

    does remotefilepath have an adminshare (c$)

    if so create a new share CDrive for proxy account

     

    Monday, March 10, 2008 10:33 AM
  • Hi AlunJ,

     

    Thanks for your quick response.

     

    I even tried creating a proxy with the domain credential and tried executing the Job. Still, its throwing the same error.

    Domain\User is given full control on the shared drive.

     

    Regards,

    Sriram.

    Monday, March 10, 2008 11:09 AM
  • Can you give me the exact path name.

     

    thanks

     

     

    Monday, March 10, 2008 11:21 AM
  • Hi AlunJ,

     

    I tried to write the file to a shared folder in C: drive in remote location. It is working fine. The Job is success.

    But, when i tried to write the file to a shared folder in E: drive, its failed.

     

    Means, is it that the shared folder must be in C: drive share?

     

    Regards,

    Sriram.

     

     

    Monday, March 10, 2008 11:31 AM
  • Create a new 'share' with rights for the proxy users into the driectory with the file

     

    then use

    \\servername\share\filename

     

     

    Monday, March 10, 2008 11:48 AM
  •  Sriram979 wrote:

    Hi AlunJ,

     

    I tried to write the file to a shared folder in C: drive in remote location. It is working fine. The Job is success.

    But, when i tried to write the file to a shared folder in E: drive, its failed.

     

    Means, is it that the shared folder must be in C: drive share?

     

    Regards,

    Sriram.

     

     

     

    No - there are no restrictions in SSIS related to the C drive. You may want to speak to the person or team who manages this server to see if there are disk quotas configured for the E drive, or if there are any other restrictions in place. There are hundreds of ways to lock down a server, and not all of them are obvious...

    Monday, March 10, 2008 12:13 PM
  • I had the same issue and it turned out to be Permission Issue.

    I gave full rights to that folder to any user that was able to log into server and Issue was resolved.

    Tuesday, January 10, 2012 9:21 PM