CODEWITHSUNDEEP
ssis
Xile
Xile

Reputation: 27

Run SSIS package manually under a specific account

I have a package stored in the file system. I must run the package manually therefore I open MSSMS with "Integration Services" and I use "Windows Authentication". The package is executed under my account. Is any setting in Integration Services to execute the package under another account? My windows account is too powerful so my goal is to execute the package under a specific account which has some restrictions.

Upvotes: 0

Views: 3041

Answers (1)

billinkc
billinkc

Reputation: 61249

Three different methods come to mind.

Method one

If you like your current approach of opening SSMS using the Integration Services object type to find the package and run it, keep doing that. The trick is that when you run the program, you need to tell Windows you'd like to run as another user. Run SSIS package using T-SQL under different account You can either Ctrl-Shift-Right click to bring up the UI for this or use the command line and RunAs

If you know where the package lives (file system vs database), you can remove your mouse clicks by building out the command line arguments dtexec.exe /file C:\packages\are\here\mypacakage.dtsx

Method two

Open Windows TasK Scheduler and define a job to use the above command line call (dtexec) but don't schedule it so it's an ad hoc job. Windows Task Scheduler understands the concept of impersonating other users so it'll prompt you for the credentials to run the process under.

Method three

Similar to two, but here we'll use SQL Agent to do our dirty work. Again, it's an unscheduled job but the nice thing about using Agent to run the package is that the SSIS job step type "knows" how to build out the commands for dtexec.

To make this work, you will need to create a Credential and then a Proxy for the credential and then allow the proxy to be used in job steps of type Integration Services.

I usually have SSMS open so I'd likely go method 3 and launch the job with my phenomenal cosmic power which then gets squished down into an itty bitty peon account

Upvotes: 1

Related Questions