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

 

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

 

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?

 

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]

 

Thanks a lot! The proxy definition did the job.

Snir

 

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