Schedule Job Keep on failing!

Hi,

Actually i saved a SSIS package into a SQL Server 2005, then scheduled to run a job from SQL Agent, but, the job keep on failing and no error message was returned in order for me to toubleshoot.

After the job failed, i restart the job again, and it actually continue to run, but unfortunately, it stopped again after some time.

And, before i sheduled the job, i actually tried to run the package itself in SQL Server Business Intelligent, it runs suceessfully.

In the package, i actually set the ProtectionLevel to DontSaveSensitive.

Another option that I used to run the pakage was using dtexex.exe:

dtexec.exe /FILE "E:\Package.dtsx" /MAXCONCURRENT " -1
"

But, the error message returned also not so useful.

So, can someone tell me how to troubleshoot this problem? What exactly the problem is that cause the job keep failing?

Thanks.

Hi, I dont know exactly about your problem but I hope this helps:

If you change the accounts for the SQL Server services you have to use the SQL Server Configuration Manager!

Otherwise (doing it by windows/services) the account wont be given the neccessary permissions in SQL Server.

Try to run SQL Server service and SQL Server Agent under the same account.

|||

New user to SQl 2005 wrote:

Hi,

Actually i saved a SSIS package into a SQL Server 2005, then scheduled to run a job from SQL Agent, but, the job keep on failing and no error message was returned in order for me to toubleshoot.

After the job failed, i restart the job again, and it actually continue to run, but unfortunately, it stopped again after some time.

And, before i sheduled the job, i actually tried to run the package itself in SQL Server Business Intelligent, it runs suceessfully.

In the package, i actually set the ProtectionLevel to DontSaveSensitive.

Another option that I used to run the pakage was using dtexex.exe:

dtexec.exe /FILE "E:\Package.dtsx" /MAXCONCURRENT " -1
"

But, the error message returned also not so useful.

So, can someone tell me how to troubleshoot this problem? What exactly the problem is that cause the job keep failing?

Thanks.

It might be helpfull if you post the error. Most of the time this kind of error are due to permissions: Protection level of the packge and/or permission of the account running SQL Agent service; so check that twice...

Rafael Salas

|||

Hi Suncream,

Actuallt the SQL Server service and SQL Server Agent service are using the same account (Local System). But the job anyhow will stop in the middle of executing.... But anyhow, when continue to start the job again, it still continue to run. And the error message returned:

Date 11/1/2006 5:49:02 PM
Log Job History (EXEC Package)

Step ID 2
Server SQLServer2005
Job Name EXEC Package
Step Name EXEC Package_Step2
Duration 02:24:19
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: SQLServer2005\SYSTEM. The package execution failed. The step failed.

Date 11/1/2006 5:48:35 PM
Log Job History (EXEC Package)

Step ID 0
Server SQLServer2005
Job Name EXEC Package
Step Name (Job outcome)
Duration 02:24:46
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. The Job was invoked by User sa. The last step to run was step 2 (EXEC Package_Step2). The job was requested to start at step 1 (EXEC Package_Step1).

I still don't have any idea of solving this problem. Please help!!

Thanks.

|||Hi,
If using this protection level you would have to use configuration files to pass the credentials to your package at runtime when executed from the SQL Agent subsystem.
Try some research on this forum around Configuration, you will find plenty of answers.
Philippe|||

Hi Philippe,

Yes, actually I'm using a configuration file to pass all the credentials to my package at runtime when executing the package from SQL Agent. And the job is also running but just that not sure what has caused it to stop after running some time.

Do you have any idea?

Thanks.

|||When looking at your config file, did you set the format to be similar to the following?
Note that the password is not passed as the wizard suggest to put it in a separate xml tag (it would get stripped off the config).

<?xml

version="1.0" ?>

- <DTSConfiguration>

- <Configuration ConfiguredType="Property"

Path="\Package.Connections[datamart.SM.oledb].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Data Source=datamart;User ID=ssis

agent;Password=VerySecret741;Initial Catalog=SM;Provider=SQLOLEDB.1;Persist Security

Info=True;</ConfiguredValue>

</Configuration>

</DTSConfiguration>

That would hold true for any provider you are connecting to.
Philippe|||Also try to set-up a text file SSIS log to see if you get any input.
Logs are not always generating errors logs as they should and this is a known problem but text logs are the best bet you have on hand.

On a personal note about logs, I would add to it that SQL Agent NEVER give a clue of what is wrong in an ssis package.
One can set-up an ssis log to be recorded in the MSDB database or in the text file.
MSDB is cool because you can easily generate a report but Text report seems to provide a little more insights of what is going wrong.
It is just not comfortable to work with text logs and they seems to be somewhat incomplete anyways, depending when the error occurs (pre-validation as an example) then text log is not helping you.

The problem here is that when you work with external datasources like Oracle, if Oracle is offline at the time, the log file does not tell you nothing while if you were working online you would get a tnsname error.

Philippe|||

Hi Philippe,

Below is the configuration file:

<?xml version="1.0"?>
<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[DB].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=Server;User ID=sa;Initial Catalog=DB;Provider=SQLNCLI.1;Auto Translate=False; Password=sa;</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[DB].Properties[InitialCatalog]" ValueType="String">
<ConfiguredValue>DB</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[DB].Properties[Password]" ValueType="String">
<ConfiguredValue>sa</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[DB].Properties[ServerName]" ValueType="String">
<ConfiguredValue>Server</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType="Property" Path="\Package.Connections[DB].Properties[UserName]" ValueType="String">
<ConfiguredValue>sa</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType="Property" Path="\Package.Properties[ProtectionLevel]" ValueType="Int32">
<ConfiguredValue>0</ConfiguredValue>
</Configuration>
</DTSConfiguration>

And I think it's the same as yours.

So, do you think that the configuration is correct?

Thanks

|||

Judging by the error you posted:

Date 11/1/2006 5:49:02 PM
Log Job History (EXEC Package)

Step ID 2
Server SQLServer2005
Job Name EXEC Package
Step Name EXEC Package_Step2
Duration 02:24:19
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: SQLServer2005\SYSTEM. The package execution failed. The step failed.

it seems you are running the package in SQL Server agent as an 'SQL Server Integretaion Services Package' step type. If you use 'Operative System(CmdExec)' as step type you will receive more details on the actual error. You can use DTExecUI to generate the command line you will need (http://msdn2.microsoft.com/en-US/library/ms141707.aspx); but in general should look like:

DTEXEC /SQL "\SSIS_Package_Name" /SERVER "Server\Instance" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

This link have some example of what I am saying:

http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Rafael Salas

|||

Judging by the error you posted:

Date 11/1/2006 5:49:02 PM
Log Job History (EXEC Package)

Step ID 2
Server SQLServer2005
Job Name EXEC Package
Step Name EXEC Package_Step2
Duration 02:24:19
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: SQLServer2005\SYSTEM. The package execution failed. The step failed.

it seems you are running the package in SQL Server agent as an 'SQL Server Integretaion Services Package' step type. If you use 'Operative System(CmdExec)' as step type you will receive more details on the actual error. You can use DTExecUI to generate the command line you will need (http://msdn2.microsoft.com/en-US/library/ms141707.aspx); but in general should look like:

DTEXEC /SQL "\SSIS_Package_Name" /SERVER "Server\Instance" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

This link have some example of what I am saying:

http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Rafael Salas