0

How to execute SSIS package from stored procedure? At this moment I am able to do this by using Windows authentication. However, I must do it using SQL Server authentication, but SQL Server requires Windows authenticated user.

  • If something in the package requires permissions to external folders/files then you may need to grant the user that the SQL Server service is running as permissions to those resources. – Arin Taylor Feb 2 '17 at 21:06
  • What version of SQL Server are you using and are you using SSISDB to store the packages? – blobbles Feb 2 '17 at 21:39
  • 2012 in both cases – user114705 Feb 2 '17 at 21:40
  • Is SSISDB being used to store the packages? Or are you storing packages on the file system? – blobbles Feb 2 '17 at 21:41
  • 1
    What? That did not answer my question... file system or SSISDB? – blobbles Feb 2 '17 at 21:48
1

If you are using SSISDB (which I think you are?), you can Execute a set of standard stored procedures that are in the SSISDB. These are outlined in the MSDN Article here.

This article also states the permissions that are required to run these procedures. It sounds like you need to set up a SQL Server login which has these permissions. How to describe this is located here. You will need to do this with your existing Windows Authenticated user if it has permission to allocate permissions (ALTER ANY LOGIN or ALTER LOGIN permissions).

You may also need to create a user who is mapped to this login as described here.

Your question is not very specific so without further information about a particular problem you are having, it is difficult to give a clear answer.

  • The problem is about these procedures (you named them standard). When I call any of them using SQL Authenticated user I got error something like: you must use window authenticated user – user114705 Feb 3 '17 at 16:39
  • Can you give us a screenshot of this error? As far as I know, SQL Server should allow you to use either a Windows Authenticated user or a SQL Server User as long as they have the appropriate permissions as specified. It should not matter which type of user it is, the only important thing is their permissions. – blobbles Feb 6 '17 at 21:22
  • One thing to remember is that the SSIS packages are run under the permission of the SQL Server Service account. So this account must have permission across the file shares that the package elements are accessing. So in your package, if you are pointing to a file: "\\filesharename\foldername\textfile.txt", the SQL Server Service account needs to have permission to access this file. Without this permission in place you may get the error you are talking about. – blobbles Feb 6 '17 at 21:45
  • Once again: Logging with SQL Authentication and calling start_execution I got "You should use winodws authenticated user", however logging on windows authenticated user I didn't get an error. – user114705 Feb 7 '17 at 16:11
  • Does your SQL Server Service Account have all appropriate permissions to all the fileshares that are in your package? You aren't being entirely helpful with your explanations. You should edit your question and be specific about the steps you are taking to receive the error. You may know what you are doing but we have no idea and cannot see the errors or your screen. Screenshots and a list of steps taken to get the error is what is required to give you an answer. You have written 3 lines and expect us to solve a basically unspecified problem. – blobbles Feb 7 '17 at 19:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy