The SSIS package does not run using SQL Server Agent job

by **john zhan** » Sat, 22 Jan 2011 08:55:06

Dear All,

My SSIS package is "Extract" data from Oracle and Load into SQL Server.

The SSIS package does not run when I call the SSIS package from a SQL Server Agent job step

I have gone through all the steps in the doc http://www.veryComputer.com/, but it still doesn't work.

Is it because of connection to "Oracle" database and SQL Server Agent service could not recognise the Authentication information to Oracle

Why I am saying this is becuase it works if I change the package to "Extract" data only from SQL Server.

Anyone has experience on this problem

Many Thanks in Advance

John

by **Rafael Sala** » Mon, 24 Jan 2011 09:56:07

Could post the error

by **john zhan** » Tue, 25 Jan 2011 11:58:09

The error is simple but no much useful information.

"Executed as user: AusDomain\chuanz. The package execution failed. The step failed."

I have turned on the logging, unfortunately there is no log file generated.

Note, I started the SQL Server and SQL Server Agent service via "Local System".

by **jwelc** » Wed, 26 Jan 2011 10:57:08

Are you using configurations to set your connection strings Also, are you attempting to use Windows Authentication with Oracle

by **jwelc** » Thu, 27 Jan 2011 12:59:10

Try using a database user and password to connect to Oracle, rather than Windows Authentication.

by **Igor** » Fri, 28 Jan 2011 15:01:12

Dear All,

My SSIS package is "Extract" data from Oracle and Load into SQL Server.

The SSIS package does not run when I call the SSIS package from a SQL Server Agent job step

So from SQL Server Business Intelligence Development Studio it works.

How do you connect to Oracle in the package Do you use "save password" in Connection Managers
Try to change the Package Security from "Encrypt with user key" to "encrypt with password", import package to SSIS with same password and create a job to launch the package (again with same password)

I have gone through all the steps in the doc http://support.microsoft.com/kb/918760, but it still doesn't work.

Is it because of connection to "Oracle" database and SQL Server Agent service could not recognise the Authentication information to Oracle

Why I am saying this is becuase it works if I change the package to "Extract" data only from SQL Server.

Anyone has experience on this problem

Many Thanks in Advance

John

Your job steps should have a command line that looks like:

Code Snippet

/DTS "\MSDB\Carvico" /SERVER SRFV0206 /DECRYPT <your_passowrd> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

by **Rafael Sala** » Sat, 29 Jan 2011 14:00:11

I am afraid you are not following all the recomendatgions in the KB article. One of them is to use is to use dtexec when running tpackage via SS agent. By doing so you will get more detail errors (see bellow teh section I am taking about). Uhe se a CmdExec step type in the agent (rather than SSIS step type). The package logging is not helpfull here because the error is happening even before the package gets executed (failing validation may be).

Notice that by changing the step type in the job won't fix the problem; but hopefully will give you a better clue about what is wrong

Code Snippet

About the exec subsystem command and output information

By using the exec subsystem command approach, you add verbose console logging switches to the SSIS command line to call the Dtexec.exe SSIS command-line executable file. Additionally, you use the Advanced job feature of the output file. You can also use the Include Step Output in the history option to redirect the logging information to a file or to the SQL Server Agent Job History.

The following is an example of a command line:

dtexec.exe /FILE "C:\_work\SSISPackages\ProtectionLevelTest\ProtectionLevelTest\AgentTesting.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING V  /CONSOLELOG NCOSGXMT

by **john zhan** » Sun, 30 Jan 2011 16:02:13

Rafael,

Thanks for your suggestion.

I tried exec subsystem command approach, it works. No error return.

by **john zhan** » Mon, 31 Jan 2011 18:04:15

Thanks IgorB,

I use Connection Manager to connect to Oracle by Username and Password and I did tick "Save my password" box.

In your approach,

1) "Encrypt with password" is "EncryptAllWithPassword" or "EncryptSensitiveWithPassword"

2) Why do I need to import package to SSIS since the package is already created in SSIS

3) The command line is generated automatically or input manually

John

by **john zhan** » Tue, 01 Feb 2011 17:03:14

Dear Jwelch,

Thanks for your suggestion,

I did use "Oracle user and password" to connect to Oracle. I am not sure whether there is a way to use Windows Authentication to connect to Oracle

by **Igor** » Wed, 02 Feb 2011 19:05:16

1) in my case it is "encrypt sensitive with password" but I think this depends on the objects that you have in your package.

2) You create the package in "SQL Server Business Intelligence Development Studio", then you should "publish" the package to a SSIS server.
In your case, you probably created a SQL Agent job step of type "SQL Server Integration Service Package" using the package source "File server", but this doesn't means that SSIS Package is created in SSIS. If you move the folder from which you selected the package, the job won't work any more.

3) command line is generated automatically. The important part is the /DECRYPT <your_passowrd>