Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Shedule agent

Author  Topic 

tboonleong
Starting Member

24 Posts
Posted - 2011-12-02 : 05:30:12
12/02/2011 15:27:40,testing2,Error,1,AOKEY\SQL2008R2,testing2,dsdsa,,Executed as user: AOKEY\Administrator.
Microsoft (R) SQL Server Execute Package Utility Version 10.50.2425.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.
Argument "AFES" for option "connection" is not valid.
The command line parameters are invalid. The step failed.,00:00:00,0,0,,,,0

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2011-12-02 : 05:41:13
Not much to go on. When does this happen?

It looks like it is trying to execute a step (maybe with an SSIS package) but your title suggests it is trying to start the agent.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

tboonleong
Starting Member

24 Posts
Posted - 2011-12-02 : 05:43:08
When I execute at the schedule agent then it hit error as below :

12/02/2011 15:27:40,testing2,Error,1,servername\SQL2008R2,testing2,dsdsa,,Executed as user: Servername\Administrator.
Microsoft (R) SQL Server Execute Package Utility Version 10.50.2425.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.
Argument "AFASE" for option "connection" is not valid.
The command line parameters are invalid. The step failed.,00:00:00,0,0,,,,0


At the begining I taught it was scheduling agent set wrongly. but tried using other package by using the same schduling agent, then it work fine.

I suspect the data source connection problem. but when i execute then package manualy thru the project ssis , then no problem.....
Appreciate for any help.
Thanks in advance.
Go to Top of Page

tboonleong
Starting Member

24 Posts
Posted - 2011-12-02 : 05:45:58
I had tried to execute the package at the ssis which been deployed at the ssis server then it work fine also.
only the problem is at the sql agent .
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-02 : 05:46:25
looks like problem in confuring values for execute package task. how are you calling package? also is it stored in server or filesystem?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-02 : 05:48:17
quote:
Originally posted by tboonleong

I had tried to execute the package at the ssis which been deployed at the ssis server then it work fine also.
only the problem is at the sql agent .

is the service account having necessary permissions to access package from server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

tboonleong
Starting Member

24 Posts
Posted - 2011-12-02 : 05:54:14
SSIS package store at server not as file.

I use the same sql agent job and change other package with different data source connection then it fail.
Go to Top of Page

tboonleong
Starting Member

24 Posts
Posted - 2011-12-02 : 05:56:09
Sorry to collect the previous reply

SSIS package store at server not as file.

I use the same sql agent job and change other package with different data source connection then it work fine.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-02 : 06:03:04
that means sql agent account doesnt have ability to connect to db which package is trying to access

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

tboonleong
Starting Member

24 Posts
Posted - 2011-12-02 : 06:29:52
I had tried just now to remove the related data source (sql connection to another server) inside the ssis package, then the package can run ok using the sql agent job.

Any idea why the data source connection cause the ssis package failed when execute it at the sql agent job ?

but execute it at ssis project maunualy not problem.... dont realy understand the ....???
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-02 : 06:34:59
when you execute from ssis project it runs under your credential
when run from sql agent, it runs under service account
as told before, I guess service account doesnt have access to other db which is why it fails

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

tboonleong
Starting Member

24 Posts
Posted - 2011-12-02 : 06:39:23

My pc is sql server 2008R2 with ssis package.
The ssis package have a data source connection which connected to the sql 2000.

At the sql 2000, i created a sql login with role and permission.
So the SSIS package at the SQL 2008R2 can established the connection using that login.

but why this will affect to the ssis when run at the sql agent job at sql 2008 R2 server?



Hope to get the help.
thanks again
Go to Top of Page

tboonleong
Starting Member

24 Posts
Posted - 2011-12-02 : 06:41:44
May I know how to set the service account to able access into the database at the sql server 2000.

Thanks for your reply.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-02 : 06:43:04
quote:
Originally posted by tboonleong


My pc is sql server 2008R2 with ssis package.
The ssis package have a data source connection which connected to the sql 2000.

At the sql 2000, i created a sql login with role and permission.
So the SSIS package at the SQL 2008R2 can established the connection using that login.

but why this will affect to the ssis when run at the sql agent job at sql 2008 R2 server?



Hope to get the help.
thanks again


i dont think you can use sql login as it would require a password and under default protection level, it wont get saved in package. hence when running from job, it wont be able to get password and connection fails
why not use windows authentication and add server agent account as a valid login inside sql 2000 db?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

tboonleong
Starting Member

24 Posts
Posted - 2011-12-05 : 04:06:38
why not use windows authentication and add server agent account as a valid login inside sql 2000 db?
====> dont know how to solve this as what you had suggested.

sorry that I can undestand the full picuture.
Thanks a lot if you can provide the detials.
Go to Top of Page

tboonleong
Starting Member

24 Posts
Posted - 2011-12-05 : 04:09:30
why not use windows authentication and add server agent account as a valid login inside sql 2000 db?
====> dont know how to solve this as what you had suggested.

sorry that I can not realy undestand the full picuture.
Thanks a lot if you can provide the detials.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-05 : 04:20:32
do you've a windows login created in database through which you can login?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

tboonleong
Starting Member

24 Posts
Posted - 2011-12-05 : 04:42:36
This sql 2008r2 have a window login and also create this user login into the sql 2008 R2 server. The ssis package at this ssis server been executed by the sql agent job. This ssis package have conncection to the remote server sql 2000. I had created a user at the sql login for this remote sql 2000 server and this login already set in the ssis package for data source connection.
So what else I can do now to make the things work?

Realy appreciate for you helping.
Thanks again.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-05 : 05:02:29
configure the sql sgent job to use windows account instead.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site