Post reply

Execute SSIS Package from Web Service

  • June 6, 2008 at 9:23 am

    #129390

    Hi guys,

    I am currently working on a DW project where I am using SSIS package to import raw data files.

    One of the client requirements is to be able to execute the package from the Web.

    I came across this article on ms site http://msdn.microsoft.com/de-de/library/ms403355.aspx.

    My package is deployed to the file system.

    I just need to know if anybody tried this option

    OR any of the below options

    like creating an sql job to execute the package OR using xp_cmdshell to execute the package within a procedure.

    Thanks

  • June 6, 2008 at 9:45 am

    #825554

    I've just gotten this to work using a stored procedure that starts an unscheduled job that executes a package. The web application executes the stored procedure using a SQL Server login. I had to create a security credential and a SQL Agent proxy so the job could be started by a non-sysadmin account.

    Greg

  • June 6, 2008 at 10:30 am

    #825577

    Thanks Greg,

    I figured that would be the best option. I am currently working that route, I will keep you guys posted as how it goes..

  • June 6, 2008 at 10:46 am

    #825588

    Good luck. I'll be watching to see if I can provide any more help so you can learn from my mistakes!

    Greg

  • June 6, 2008 at 12:34 pm

    #825651

    I would avoid xp_cmdshell - it is disabled by default for a reason.

    Using the job agent is pretty easy and works well. If, however, the SSIS server is the web server, using the SSIS object model to execute the package will give you the ability to present lots of feedback on the web page rather than just an animated gif.

  • June 6, 2008 at 1:41 pm

    #825693

    Hi Guys,

    I have been trying to execute the stored procedure which in turn calls

    Exec msdb.dbo.sp_start_job @jobname

    If I run this sp using windows authentication, it works

    if I run it using SQL login which has execute permission to all the sp's, it throws an error saying,

    "Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account."

    How to create the proxy account?

    Any help is appreciated..

    Thanks

  • June 9, 2008 at 9:06 am

    #826150

    That's why you need the proxy. Non-sysadmins, by default, cant' start and manage jobs. Here's what I did:

    1. Create a SQL server login that will be used for SSIS execution. I made it different than the application login for a system so I could keep the password from developers.

    2. Create a user for the login in msdb and grant membership in SQLAgentUserRole. This role will allow the user to start jobs that it owns.

    3. Create a user for the login in master with no permissions. This is to allow the execution of xp_sqlagent_is_starting and xp_sqlagent_notify, which are executed from msdb.dbo.sp_start_job.

    4. Create a security credential that contains the authentication information for the account that runs SQL Server Agent.

    a.Expand Security

    b.Right-click on Credentials and select ‘New Credential’

    5. Create a SQL Server proxy account for SSIS package execution. Use the previously created credential name, make it active to Integration Services package execution, and make the SQL Server login a principal.

    a.Expand SQL Server Agent

    b.Right-click on Proxies and select ‘New Proxy’

    6. Create an unscheduled job, owned by the SQL Server login, with a step that executes the SSIS package.

    a.Set Type to ‘SQL Server Integration Services Package’.

    b.Set Run as to the proxy account

    7. Create a stored procedure in the user database that executes sp_start_job as the login that is a principle of the SQL Server Agent proxy. The procedure uses the EXECUTE AS clause to switch security context.

    Grant EXECUTE permission on the stored procedure to the application login used in the database.

    Greg

  • June 9, 2008 at 9:10 am

    #826155

    Thanks Greg,

    Will try that..

  • June 10, 2008 at 9:55 am

    #826776

    Hi all,

    I finally ended up using the command line option of the SQL Job.

    i.e Job Setup Properties -> General -> Type (Operating System (CmdExec)) -> Command line ->

    DTExec.exe \FILE "package.dtsx" \DECRYPT "password".

    Calling the job using the stored procedure like Greg mentioned earlier..

    Thanks

  • June 10, 2008 at 12:40 pm

    #826898

    Guys,

    Finally my Web service is working now. I can execute the package and get the status of the sql job as well.

    Thanks all for your help.

  • June 11, 2008 at 9:38 am

    #827360

    If you set the job step type to "SQL Server Integration Services Package" instead of "CmdExec" and use a proxy account, you could reduce the permissions required to run the job.

    Greg

  • June 11, 2008 at 1:54 pm

    #827504

    Hi,

    Well the issue I ran into was, I used EncryptSensitiveDataWithPassword option in my Package so I could not provide the password after \DECRYPT switch while using the type - SSIS.

  • June 12, 2008 at 8:41 am

    #827907

    Interesting. I was able to provide the password in my job...

    Greg

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy