title: Execute SSIS package from stored procedure as proxy user without xp_cmdshell
body: <p>I am trying to run an SSIS package through a stored procedure, but I am getting an <code>Access is denied</code> error when I try to import a CSV.</p>
  
  <p>I put the package inside a job and ran it and it worked as long as I used a proxy account. I am trying to replicate that proxy account to the stored procedure call <strong>without</strong> using <code>xp_cmdshell</code>. I also ran this package inside Visual Studio and it ran smoothly.</p>
  
  <p>My SSIS package is simple: It imports a CSV file from the network, converts the data to <code>varchar</code>, and stores the data into a table.</p>
  
  <p>Even my sysadmin was not able to successfully run the stored procedure.</p>
  
  <p>My stored procedure looks like this:  </p>
  
  <pre><code>ALTER PROCEDURE [dbo].[spImportFile] 
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      DECLARE @execution_id bigint
      EXEC SSISDB.CATALOG.create_execution
          @folder_name = 'folder_name',
          @project_name = 'project_name',
          @package_name = 'package_name.dtsx',
          @use32bitruntime = 1,
          @execution_id = @execution_id output
  
      EXEC SSISDB.CATALOG.start_execution @execution_id
  END
  </code></pre>
  
  <p>My question is, how can I programmatically use a proxy user inside this stored procedure without using <code>xp_cmdshell</code>?</p>
  
  <hr>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>I am now trying to impersonate my proxy user thanks to <a href="https://stackoverflow.com/users/181965/billinkc">billinkc</a>, but now I am running into this error when I execute the SSIS package:</p>
  
  <blockquote>
    <p>The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again.</p>
  </blockquote>
  
  <p>Here is my altered code:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[spImportFile] 
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      EXECUTE AS LOGIN = 'domain\credentials'
  
      DECLARE @execution_id bigint
      EXEC SSISDB.CATALOG.create_execution
          @folder_name = 'folder_name',
          @project_name = 'project_name',
          @package_name = 'package_name.dtsx',
          @use32bitruntime = 1,
          @execution_id = @execution_id output
  
      EXEC SSISDB.CATALOG.start_execution @execution_id -- &lt;&lt;&lt;&lt; ERROR HERE!
  
      REVERT
  
  END
  </code></pre>
  
  <p>I successfully tested <code>EXECUTE AS LOGIN</code> and <code>REVERT</code> without <code>start_execution</code> by looking into a system table I wouldn't usually have access to.</p>