Running an SSIS package from SQL server job ajent

Discussion in 'microsoft.public.sqlserver.dts' started by shnel, Jan 12, 2009.

  1. shnel

    shnel Guest

    Hi,
    I am trying to run an SSIS package from SQL server agent.
    The package has one script task that uses an ADO connection.
    When running the package via Execute Package Utility there is no
    problem.

    On the SQL agent I get the error:
    Executed as user: COSMO\SYSTEM. Microsoft (R) SQL Server Execute
    Package Utility Version 9.00.3042.00 for 32-bit Copyright (C)
    Microsoft Corp 1984-2005. All rights reserved. Started: 9:26:09
    AM Error: 2009-01-12 09:26:10.51 Code: 0x00000002 Source:
    Update WD Users table Description: The script threw an exception:
    Cannot create ActiveX component. End Error DTExec: The package
    execution returned DTSER_FAILURE (1). Started: 9:26:09 AM Finished:
    9:26:10 AM Elapsed: 0.797 seconds. The package execution failed.
    The step failed.

    Any ideas?

    Thanks,
    Snir
     
    shnel, Jan 12, 2009
    #1
    1. Advertisements

  2. shnel

    Todd C Guest

    Does the account that runs SQL Agent and/or SSIS have the necessare
    permissions and rights as you. When you run the package via DTEXEC command
    line or DTExecUI GUI, you are executing as yourself.

    Check your account permissions.

    HTH
     
    Todd C, Jan 12, 2009
    #2
    1. Advertisements

  3. shnel

    shnel Guest

    SQL Server agent runs as LocalSystem and SSIS runs as NetworkService
    I can't figure out what privileges do they have exactly.I ran the
    package as a local system administrator that have permissions on the
    requires network folders.
    Can I define a specific job to run as a specific user?
     
    shnel, Jan 15, 2009
    #3
  4. shnel

    Todd C Guest

    Be definition, a Local System account cannot be given network rights, nor is
    it aware of anything outside its own box (I think) so the problem might lay
    there.

    However, you can create a Proxy and a Credentials that work together. I
    think you need to create the Credentials first, then create the Proxy account
    that uses those Credentials. You also set up what functions said Proxy can be
    used for. After that, you set up your SQL Agent job to run under the security
    context of the Proxy.

    Now even if SQL Agent is running under Local System, when it executes that
    specific job, it will access the outside resources using the Proxy.

    Godd Luck.
    --
    Todd C

    [If this response was helpful, please indicate by clicking the appropriate
    answer at the bottom]


     
    Todd C, Jan 15, 2009
    #4
  5. shnel

    shnel Guest

    Thanks a lot! The proxy definition did the job.

    Snir
     
    shnel, Jan 19, 2009
    #5
  6. Hello,
    I have the same problem. I set up the credential and proxy and the Agent is perfectly able to run packages that involve processing files stored in the local server for example.

    The problem is now with a package that needs to save a file on a network location. I runs fine from Visual Studio, and even from the Execute Package utility. However, the agent is not able to run it. Unfortunately the history log of the job doesn't give any hints whatsoever.

    Would any of you have an idea of what's going on?

    Thanks!



    shnel wrote:

    Re: Running an SSIS package from SQL server job ajent
    20-Jan-09

    is
    ay
    ount
    n be
    rity
    t
    e
    mmand
    riate
    =A0 Source:
    xception:
    e
    Finished:
    ailed.

    Thanks a lot! The proxy definition did the job.

    Snir

    Previous Posts In This Thread:

    Does the account that runs SQL Agent and/or SSIS have the necessare
    Does the account that runs SQL Agent and/or SSIS have the necessare
    permissions and rights as you. When you run the package via DTEXEC command
    line or DTExecUI GUI, you are executing as yourself.

    Check your account permissions.

    HTH
    --
    Todd C

    [If this response was helpful, please indicate by clicking the appropriate
    answer at the bottom]


    :

    Running an SSIS package from SQL server job ajent
    Hi,
    I am trying to run an SSIS package from SQL server agent.
    The package has one script task that uses an ADO connection.
    When running the package via Execute Package Utility there is no
    problem.

    On the SQL agent I get the error:
    Executed as user: COSMO\SYSTEM. Microsoft (R) SQL Server Execute
    Package Utility Version 9.00.3042.00 for 32-bit Copyright (C)
    Microsoft Corp 1984-2005. All rights reserved. Started: 9:26:09
    AM Error: 2009-01-12 09:26:10.51 Code: 0x00000002 Source:
    Update WD Users table Description: The script threw an exception:
    Cannot create ActiveX component. End Error DTExec: The package
    execution returned DTSER_FAILURE (1). Started: 9:26:09 AM Finished:
    9:26:10 AM Elapsed: 0.797 seconds. The package execution failed.
    The step failed.

    Any ideas?

    Thanks,
    Snir

    Be definition, a Local System account cannot be given network rights, nor is
    Be definition, a Local System account cannot be given network rights, nor is
    it aware of anything outside its own box (I think) so the problem might lay
    there.

    However, you can create a Proxy and a Credentials that work together. I
    think you need to create the Credentials first, then create the Proxy account
    that uses those Credentials. You also set up what functions said Proxy can be
    used for. After that, you set up your SQL Agent job to run under the security
    context of the Proxy.

    Now even if SQL Agent is running under Local System, when it executes that
    specific job, it will access the outside resources using the Proxy.

    Godd Luck.
    --
    Todd C

    [If this response was helpful, please indicate by clicking the appropriate
    answer at the bottom]


    :

    Re: Running an SSIS package from SQL server job ajent
    d
    e
    09
    urce:
    tion:
    shed:
    d.

    SQL Server agent runs as LocalSystem and SSIS runs as NetworkService
    I can't figure out what privileges do they have exactly.I ran the
    package as a local system administrator that have permissions on the
    requires network folders.
    Can I define a specific job to run as a specific user?

    Re: Running an SSIS package from SQL server job ajent
    is
    ay
    ount
    n be
    rity
    t
    e
    mmand
    riate
    =A0 Source:
    xception:
    e
    Finished:
    ailed.

    Thanks a lot! The proxy definition did the job.

    Snir


    Submitted via EggHeadCafe - Software Developer Portal of Choice
    LINQ With Strings
    http://www.eggheadcafe.com/tutorial...47db-adb9-db7fe2c6ab8c/linq-with-strings.aspx
     
    Bernardo Romero, Jun 11, 2010
    #6
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.