cannot open the datafile from executing package in SSISDB

  • I have converted a package deploy mode SSIS project to project deploy mode.

    After I deployed to the server, I found one of the packages that uses a shared network file failed.

    The package uses it as a readonly source file.

    It has no problem when I ran it in package deploy mode, and there is no issue when I ran it in SSDT BI tool.

    It is only when I run it in SSISDB catalog, - execute package, I got this failed message.

    Cannot open the datafile "\\myserver\data\mytextfile.txt"

    Access denied.

    I have full access to the directory and I am dba on the SQL server.

    And as long as I know if I run from SSISDB catalogs, it is running under the context of SSMS connection user, which is my account.

    I also copied the directory in windows explorer, and open the file without any issue.

    But why I still get this error?

    Thanks

  • please check if you have any proxy account and which is having account access

  • Kerberos delegation may be the issue. See here.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I have spent a lot of time to convert packages from package deploy mode to project deploy mode, and only find this is not working for UNC path, and it needs kerboros authentication.

    This is not acceptable, windows authentication should work.

    Any workaround other than configure the server to be kerboros authentication?

    Thanks,

  • sqlfriends (6/14/2016)


    I have spent a lot of time to convert packages from package deploy mode to project deploy mode, and only find this is not working for UNC path, and it needs kerboros authentication.

    This is not acceptable, windows authentication should work.

    Any workaround other than configure the server to be kerboros authentication?

    Thanks,

    Try running the packages as SQL Agent jobs and see whether that fixes things up.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I tried using SQL agent job. Get the same error.

    If I run the package in package deploy mode that I saved on the file system, I don't have problem.

    I just don't want to go through the Hassel to create Kerberos for the server for this small UNC thing.

    And if this is the case, we will have to later configure all SQL servers to Kerberos, it is a pain.

    I will revert back to package deploy mode. which is easy and clean.

  • sqlfriends (6/14/2016)


    I tried using SQL agent job. Get the same error.

    If I run the package in package deploy mode that I saved on the file system, I don't have problem.

    I just don't want to go through the Hassel to create Kerberos for the server for this small UNC thing.

    And if this is the case, we will have to later configure all SQL servers to Kerberos, it is a pain.

    I will revert back to package deploy mode. which is easy and clean.

    And is the SQL Agent service user a domain account with access to the UNC path?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I take it back, thanks for your patience.

    I tried again with SQL agent job using SQL service account, it works this time.

    But if I run it in SSISDB catalog, which I think it is under my account, it failed.

    I do have full access to the directory.

    Any idea why it works in SQL agent but not SSISDB catalog?

    Thanks,

  • sqlfriends (6/14/2016)


    I take it back, thanks for your patience.

    I tried again with SQL agent job using SQL service account, it works this time.

    But if I run it in SSISDB catalog, which I think it is under my account, it failed.

    I do have full access to the directory.

    Any idea why it works in SQL agent but not SSISDB catalog?

    Thanks,

    I'll be honest ... I have little interest in learning enough to be able to solve the Kerberos stuff & happily leave that to DBAs and network gurus. I know only that I've had success in using SQL Agent rather than SSMS when executing packages in SSISDB, because I had problems similar to the ones you are having.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thank you.

    With this limitation, it seems I cannot use much the feature that runs package in SSISDB catalog context. But I can do it in SQl agent.

    I really do not want to configure my servers into Kerberos for this issue.

    Have you converted all your packages to project deploy mode?

    So far I did not see much advantage using project deploy mode vs package deploy mode?

    Thanks,

  • There are some serious advantages.

    Native logging.

    Project-scoped parameters and connections.

    Configuration from SSISDB environment variables.

    Are ones which come immediately to mind.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • The ability to run packages directly from T-SQL code is another.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 12 posts - 1 through 11 (of 11 total)

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