Unable to get a SSIS package to run via job scheduler. What is the best method to trap the logging?

Question

Unable to get a SSIS package to run via job scheduler. What is the best method to trap the logging?

Lynn-320580

Grasshopper

Points: 17
More actions
October 9, 2006 at 9:50 am

#174375

I have a simple data transfer package. The package runs fine when I execute it. Once it is scheduled it fails. I'm not able to find any specific errors. I've tried running under a proxy and still it fails. Anyone know how to trap the logging and how to get this job to execute to competion?

Viewing 5 posts - 1 through 4 (of 4 total)

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

dharper3 SSCrazy Points: 2391 More actions · Answer 1

Not sure what the package is doing, more info may help.

Note by default xp_cmdshell is disabled if you are calling this it will fail, other causes maybe the login that owns the job. Does the login have the privs to run ssis packages. I would suggest looking at the SQL Server error log and the Windows event logs for system, applicarion and security events.

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 2

Not sure if you saw all the references to this kind of problems when you searched for help of this problem.

There are a few possible issues, and things you can do to try and find the cause:

Package ProtectionLevel - what is this setting on in your package? The default is EncryptSensitiveWithUserid (iirc) which is great for developing, but seems to be the chief cause of having issues scheduling a package, since it is unlikely that you SQL and SQL Server Agent are using the same userId you developed with. In my (still limited) experience, the simplest solution here is to change to EncryptSensitiveWithPassword - you will have to always have to enter the password, but at least it works from agent

Deployment/Package configurations - are you using config files? Are they correct? Have you deployed to SQL or File system? If File system, does SQL Server Agent have access to the location?

Job setup - How are you executing the package? It is (imho) a lot more usable to execute the package via a Operating system step type. To do this, you need to build the DTEXEC command (use the DTEXECUI, and then add DTEXEC to the front of the command, and paste it into the command pane for the job step. Then (and this is is why this is useful), go to the advanced tab for the job step, and set up (a) the include step output in history flag, and (b) (more usefully) set the Output file flag, and have the job write it's output to a fiel for debugging.

Logging - have you enabled logging for you package - this is another valuable source of tracking what is going on, but given you're example, the package seems to be fine - it is more to do with how, and as whom, you invoke it....

Good Luck

Lynn-320580 Grasshopper Points: 17 More actions · Answer 3

I've tried all different combinations of protection level. I tried it encrypt with password, don't save sensitive and server setting. I've also changed the created by user to the same as the one running services. All the different users involved are part of the system admin group. I also setup config files to be used during execution with the password in them to see if that helps the problem. The config files are the standard that you can have all the properties captured. This was added after multiple attempts with standard setup.

I created a job and selected the drop down for it being a SSIS package. I'm not using a command line although it automatically will have it as part of the creation.

I have also tried to run it from all three ssis package store, file system and sql. Right now it is set to sql.

In a nutshell it believes that it is still encrypted and doesn't look at any setting I've tried. Even with using a proxy.

Any input would be greatly appreciated.

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 4

Well, you've certainly tried a lot of variations!

Can I recommend you try and get the SQL Job to run as a CommandLine step, and set up that text file output under advanced. The command line for a file system dtsx looks something like:

dtexec /FILE "G:\YourPackageLocation\Packages\MyPackage.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V

to run from SQL, I believe it is /SQL

to decrypt, you would specify /DECRYPT PasswordToBeUsed

I have found that the log files written out by this can be VERY useful in trapping information about specific errors that occur.

hope that helps