none
Sql Agent job cannot run with flat file connection

    Question

  • All,

    I have a SSIS package that can be run outside SQL Server Agent, but fail with SQL Server Agent for the same user login. The packages are saved in the sql server database with Windows Authentication. The protection level I used is the default one: “encrypt sensitive data with user key”

    The packages have a step to dump data into a flat file destination. The error message is

    “Cannot open the datafile” on the local drive that the user has access to.

     

    Any idea on how to fix it is appreciated?

     

    Thanks in Advance.

    Wednesday, April 18, 2007 3:38 PM

All replies

  •  jessie2 wrote:

    All,

    I have a SSIS package that can be run outside SQL Server Agent, but fail with SQL Server Agent for the same user login. The packages are saved in the sql server database with Windows Authentication. The protection level I used is the default one: “encrypt sensitive data with user key”

    The packages have a step to dump data into a flat file destination. The error message is

    “Cannot open the datafile” on the local drive that the user has access to.

    Any idea on how to fix it is appreciated?

    Thanks in Advance.



    That *what* user has access to?  Ensure that the SQL Server service user has access as well.
    Wednesday, April 18, 2007 3:42 PM
  • thanks for your reply.

     

    The user has access to the local drive for the flat file creation as well as to the sql server database.

    I can run the package on the debugger and execute from DB on the same server, but cannot run it with SQL Agent job steps.

     

     

    Thanks

    Wednesday, April 18, 2007 3:53 PM
  • Try changing the ProtectionLevel to EncryptSensitiveWithPassword before you promote.  Give the package a password.  Then in the agent job step, you can specify the password on the command line using the /De switch to dtexec.exe
    Wednesday, April 18, 2007 4:04 PM
  • I tried method 2 and 3 from this post, but still getting the same error.

    ‘Cannot open the datafile "Z:\data\example _20070418". 

     

    http://support.microsoft.com/kb/918760

    Wednesday, April 18, 2007 4:07 PM
  •  

    thanks, Phil,

     

    Could you please give detailed instruction on how to do this:

     

    you can specify the password on the command line using the /De switch to dtexec.exe.

     

     

     and also on the job step properties:

     

    as a default Run as : Sql Agent Service Account

     

    But I don’t have anything on Sql Agent Service Account, could that be a problem?


    Thanks


     

    Wednesday, April 18, 2007 4:23 PM
  • If you're running a job type of Integration Services, you can override the command line arguments.  Simply add that /De switch and the password.
    Wednesday, April 18, 2007 4:27 PM
  •  jessie2 wrote:

    I tried method 2 and 3 from this post, but still getting the same error.

    ‘Cannot open the datafile "Z:\data\example _20070418".

    http://support.microsoft.com/kb/918760



    Z: is surely a mapped drive, not a local drive as you said above.

    See this thread and ignore that it is a Novell network.
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1465383&SiteID=1
    Wednesday, April 18, 2007 4:33 PM
  •  Thanks. but where/How to set  /PERSISTENT:YES flag ?

     

    Thanks

    Wednesday, April 18, 2007 7:44 PM
  •  jessie2 wrote:

    Thanks. but where/How to set /PERSISTENT:YES flag ?

    Thanks



    net use \\server\share /PERSISTENT:YES
    Wednesday, April 18, 2007 7:48 PM
  • Hi Jesse2,

     

       I have a post about the EncryptSensitiveWithPassword setting for the SSIS package ProtectionLevel property here.

     

       Security can be tricky in SSIS. One thing to remember about scheduled SQL Agent jobs: The job executes within the security context of the account used to start the SQL Agent service. If you schedule an SSIS package using a SQL Agent job, the SSIS package is confined to the access and permissions granted this security context.

       If you use the Local System account to start the SQL Agent service, it may not be able to access a mapped drive (such a the Z: drive you mention).

     

       Setting the EncryptSensitiveWithPassword will not correct connectivity issues to files and directories - and it sounds like this is the latest issue you're facing. To address this I recommend the following steps:

    1. Create an Active Directory domain account for you SQL Agent service and change the service logon properties to use this account.

    2. Make sure this account has access to the "Z:\data\" folder on your domain.

    3. Use the full network URN instead of the mapped drive. In other words, replace "Z:\data\" with something like \\[MachineName]\[ShareName]\data\.

     

    Hope this helps,

    Andy

    Friday, April 20, 2007 10:43 AM