using xp_cmdshell to execute an SSIS package

Hello experts.

I have an SSIS package developed on my local machine that I need to put into production. It will run when a user presses a button on a website and it will run nightly as a scheduled job. My first attempt was to create a stored procedure that calls xp_cmdshell to run the .dtsx file but it looks like there could be some security holes with this approach. Are there any recommended alternatives?

Thank you.
LVL 3
dpiccoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brad2575Commented:
You can just use use the website to execute the job that you have scheduled to run nightly.  That way it would still run nightly and whenever the user presses the button (assuming you would restrict the website to whoever should have access to it only).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris MConsulting - Technology ServicesCommented:
I guess you want to give control to the user to choose when to run the job from the website.
You optionally can create an underprivileged user on the OS and database who can run this SSIS package. Then optionally may use some database trigger to handle the event where you can stop any other user from invoking the job from a different location.

I think the options are still available.
0
Reza RadConsultant, TrainerCommented:
if you want to run your ssis package, so use DTEXEC , you don't need xp_cmdshell then.

0
Reza RadConsultant, TrainerCommented:
this is more about how to run package by dtexec:
http://msdn.microsoft.com/en-us/library/ms162810.aspx
0
dpiccoAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.