running package deployed to ssis catalog (ssis 2012) and getting a mysterious folder permission issue
I'm having a problem with a File System Task that sometimes fails to create a folder on a network location depending on how the package is executed. I think this is going to come down to knowing what domain ID is getting used under these different run situations. When the create folder works, I can see who created it by checking the folder property. But when the folder doesn't get created, obviously I don't have that folder to check its property to know who was trying to create it. So
(1) Everything works from SSDT (the visual studio environment). So I know there is nothing wrong with the package itself. When I check the folder property, it says my Windows Login owns the folder. (I am assuming this is a reliable way to know who created the folder)
(2) I deploy the package to SSIS catalog and create a SQL Agent job. I execute the job and again, the folder gets created. This time, the folder owner is the agent service ID.
(3) I create a stored proc that uses SSISDB.catalog.start_execution to call this package. And here is where I run into a problem. The only way this package creates the same folder that worked in (1) and (2) is if I remote on to the SQL Server box that the stored procedure is on and execute the stored procedure from there. If I execute the stored procedure from my machine or any other machine, the package fails saying folder access denied.
Could there be some kind of "double hop" issue going on where it loses who is running the procedure when I don't execute from the SQL Server box that is hosting the procedure?
This is a "double hop" issue.
The best way to work around your problem is create a SQL Agent job, and then have your stored proc call "sp_start_job".
using sp_start_job on a catalog SSIS package defeats the whole purpose of SSIS 2012. I need to be able to pass in package run parameters using a stored proc that is going to execute the package. To do this using sp_start_job requires doing a bunch of work arounds that shouldn't be necessary. I know this problem is solvable because I have another server where this isn't a problem. It's just that I don't know what is controlling this.