cannot open the datafile from executing package in SSISDB

Question

Post reply

cannot open the datafile from executing package in SSISDB

sqlfriend

SSC Guru

Points: 52781
More actions
June 13, 2016 at 5:54 pm

#317269

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

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

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

subramanian.esakkimuthu 40485 SSCrazy Points: 2022 More actions · Answer 1

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Kerberos delegation may be the issue. See here.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

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,

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sqlfriend SSC Guru Points: 52781 More actions · Answer 5

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.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

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?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sqlfriend SSC Guru Points: 52781 More actions · Answer 7

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,

Phil Parkin SSC Guru Points: 246960 More actions · Answer 8

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sqlfriend SSC Guru Points: 52781 More actions · Answer 9

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,

Phil Parkin SSC Guru Points: 246960 More actions · Answer 10

There are some serious advantages.

Native logging.

Project-scoped parameters and connections.

Configuration from SSISDB environment variables.

Are ones which come immediately to mind.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246960 More actions · Answer 11

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin