August 1, 2008 at 1:57 pm
What is the best practice how to run SSIS packages?
We have about 20 packages that need to be run monthly.
Right now SSIS are using Windows Authentication in Connections Managers.
There was a specific person who normally ran them manually (by opening each package) but that person is on vacation now and our Manager asked to run the packages another developer. That developer got Access Denied error message and I understand why. Because his Domain account does not have enough permissions on SQL Server 2005.
I don't like this Security solution at all.
I want to propose to the Management to:
1. Create a special "SSISuser" Domain account, grant all necessary permissions to this account
2. Create a batch that executes multiple "DTEXEC" commands and executes all our SSIS packages
3. Schedule that batch and have "Run As" SSISuser (I think you can do it in Windows Scheduler)
Am I going in the right direction?
Another option is to run SSIS using SQL Authentication
and always specify SQL Login in SSIS Connection Manager.
(this is probably easier for us now because we don't have to beg Domain Admin to create a new Domain account. We don't have internal support. It's outsourced by IBM)
I want to know all pros and cons of these two methods.
August 1, 2008 at 3:31 pm
What are you using to schedule and run the SSIS packages? Are they being run manually?
K. Brian Kelley
@kbriankelley
August 3, 2008 at 9:29 pm
Currently they run them manually.
They open SSIS in Development Studio and execute it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply