Job Server Failed

Question

Job Server Failed

Charmer

SSChampion

Points: 14903
More actions
July 18, 2012 at 10:42 am

#392775

Hi Friends,
I am trying to run a ssis package through job server but failed. i tried manually using DTEXEC.UI and it really worked. but when i go through job server it is not working. it throws connection error.
there are three connections in my package. two of them are OLEDB connection and another one is ADO.NET connection.
ADO.NET connection is getting failed when running through jo server.
Is there any thing i must watch for when using ADO.NET connection?
Help me friends..
any suggestion would be really appreciated..
Thanks,
Charmer

Viewing 15 posts - 1 through 15 (of 22 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 1

Enable detailed logging and go through the log. Chances are you have a security issue. When the package is scheduled, it runs under the credentials of the agent (or proxy). When you run it, it runs under your credentials

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

Posting the actual errors might help.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Charmer SSChampion Points: 14903 More actions · Answer 3

Koen Verbeeck (7/18/2012)
Posting the actual errors might help.

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted

07/19/2012 14:55:51,DW,Error,0,MyPC,DW,(Job outcome),,The job failed. The Job was invoked by User sa. The last step to run was step 3 (ASold). The job was requested to start at step 3 (ASold).,00:00:00,0,0,,,,0

Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Argument ".NET" for option "connection" is not valid. The command line parameters are invalid. The step failed.,00:00:00,0,0,,,,0

This is the kinda error i get..

Thanks,
Charmer

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

Try renaming your ADO.NET connection from .NET to ADONET.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Charmer SSChampion Points: 14903 More actions · Answer 5

Koen Verbeeck (7/19/2012)
Try renaming your ADO.NET connection from .NET to ADONET.

ok Koen, Let me try

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 6

Koen Verbeeck (7/19/2012)
Try renaming your ADO.NET connection from .NET to ADONET.

not worked , Koen. Still getting the ADO.NET connection error.

Thanks,
Charmer

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

Did you do something special in the Agent jobstep configuration?

Did you select the checkboxes in the connections tab?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Charmer SSChampion Points: 14903 More actions · Answer 8

Koen Verbeeck (7/19/2012)
Did you do something special in the Agent jobstep configuration?
Did you select the checkboxes in the connections tab?

yes, Koen..i did modify the connections tab with proper crendentials ....that is a problem?

FYI, i use .net connection to get the fact tables from DW database.

Thanks,
Charmer

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 9

Apparently there's a problem, otherwise the job wouldn't crash 🙂

Can you post the command line? (you can find it in the command line tab of the jobstep)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Charmer SSChampion Points: 14903 More actions · Answer 10

Koen Verbeeck (7/19/2012)
Apparently there's a problem, otherwise the job wouldn't crash 🙂
Can you post the command line? (you can find it in the command line tab of the jobstep)

/FILE "C:\Users\administrator\Desktop\DB Population\AS.dtsx" /CONNECTION ".NET connection for DW database";

"\"Data Source=Source;User ID=sa;Initial Catalog=MyDB;Persist Security Info=True;

Application Name=SSIS-Package-{859CFD36-7337-487A-8994-9F103BBCEE4A}MyDB.sa1;\""

/CONNECTION "AS Database connection";"\"Data Source=Source1;Initial Catalog=MyDB1;Provider=MSOLAP.4;

Integrated Security=SSPI;Impersonation Level=Impersonate;\"" /CONNECTION "OLEDB connection for DW database";

"\"Data Source=Destination;User ID=sa;Initial Catalog=MyDB2;Provider=SQLNCLI10.1;

Persist Security Info=True;Auto Translate=False;Application Name=SSIS-Package-{0448EBBC-D000-48CB-94B3-9CE29D82096E}MyDB1.sa;

\"" /CHECKPOINTING OFF /REPORTING E

i just modified only the credentials...

Thanks,
Charmer

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 11

At first sight, it looks OK.

(except that the passwords are missing, but I think SQL Agent leaves them out for security reasons).

The connection name still uses ".NET" though. Try removing the . in the name.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Charmer SSChampion Points: 14903 More actions · Answer 12

Koen Verbeeck (7/19/2012)
At first sight, it looks OK.
(except that the passwords are missing, but I think SQL Agent leaves them out for security reasons).
The connection name still uses ".NET" though. Try removing the . in the name.

Koen,

removing '.' from the command line or from the package itself?

Thanks,
Charmer

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 13

Both.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Charmer SSChampion Points: 14903 More actions · Answer 14

Koen Verbeeck (7/20/2012)
Both.

This is the command line after modified...

/FILE "D:\Backup\Projects Backup\Partitions_19072012\Partitions\bin\AS.dtsx" /CONNECTION "ADONET connection for DW database";"\"Data Source=MySource;User ID=sa;pwd=Pn***;Initial Catalog=DW;Persist Security Info=True;Application Name=SSIS-Package-{859CFD36-7337-487A-8994-9F103BBCEE4A}MySource.DW7.sa1;\"" /CONNECTION "AS Database connection";"\"Data Source=MySource;Initial Catalog=DB;Provider=MSOLAP.4;Integrated Security=SSPI;Impersonation Level=Impersonate;\"" /CONNECTION "OLEDB connection for DW database";"\"Data

Source=MySource;User ID=sa;pwd=Pn***;Initial Catalog=DW;Provider=SQLNCLI10.1;Persist Security Info=True;Auto Translate=False;Application Name=SSIS-Package-{0448EBBC-D000-48CB-94B3-9CE29D82096E}MySource.DBDW7.sa;\"" /X86 /CHECKPOINTING OFF /REPORTING E

Koen, Still giving error while running through SQL job server

Message

Executed as user: sa. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Argument "ADONET" for option "connection" is not valid. The command line parameters are invalid. The step failed

Thanks,
Charmer