SSIS LOGGING

Second time around w/this one. I'm running my sqlserveragent with a domain admin account and everything works well. I then switch to a domain user and the package fails. Windows app log contains ths

Package "Package1" failed.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

So I go to the sql server agent jobs and select the job I'm trying to run. I then go to Job Step Properties and under the logging tab choose SSIS log provider for Text files. What is supposed to go in the Configuration String

It will let me type a text file in there or choose from one of my "3" datasources in my package. But since I'm developing this on another machine the choice to log to the file system makes no sense.

Please do not refer me to the kb article (http://support.microsoft.com/ kbid=918760) I've read and re-read that. All that I want to do is find out why the package is failing.

Thanks.

Answer this question

SSIS LOGGING

Janice_777

DarrenSQLIS wrote:

Your DTEXEC command looks wrong, as per the first error. /P for for supplying SQL security, see Books Online "..Allows the retrieval of a package that is protected by SQL Server Authentication..."

You have a file package not a SQL stored package, so /P cannot be used, and does not make sense. Maybe you want the "/De[crypt] password" option instead.

Darren - I changed it to /De password and through the command line it works. It fails when running it through the SQL Server Agent >> Jobs >> Right Click and choose start job at Step (remrember this runs fine when under a domain amdin account). I'm pulling my hair out and thanks for staying with me on this one. I really need to have this in place and running by cob today.

westlakechen
I tried that but since my package is "password" protected it kept spitting out an error. Here is the error from cmd line

C:\Documents and Settings\xxx>dtexec.exe /FILE "F:\xxxxx\xx.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPOR
ING V /CONSOLELOG NCOSGXMT /P secretpassword

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

The File option cannot be specified with the DTS, SQL, Server, User, or Passwor
options.

C:\Documents and Settings\xxx>

If I dont enter a password here is the output

Started: 12:19:56 PM
Error: 2006-06-01 12:19:56.54
   Code: 0xC0014037
   Source: {6F6AB710-4543-4ED9-9D3D-5FE40703A1EC}
   Description: The package is encrypted with a password. The password was not
pecified, or is not correct.
End Error
Could not load package "F:\OASISDB_SSIS_PACKAGES\dts_CopyGREOORHS3.dtsx" becaus
of error 0xC0014037.
Description: Failed to remove package protection with error 0xC0014037 "The pac
age is encrypted with a password. The password was not specified, or is not cor
ect.". This occurs in the CPackage::LoadFromXML method.
Source: {6F6AB710-4543-4ED9-9D3D-5FE40703A1EC}
Started: 12:19:56 PM
Finished: 12:19:56 PM
Elapsed: 0.047 seconds
Sebastian Rojas

For logging you should select a connection. The file path may change between machines, so use a configuration or an expression to make this path "dynamic" and therefore you can set it for each environment.

Saying that your problem may well be loading the package, in which case logging will not help. See this article about using the CmdExec step and setting the job step log, Job History and Job Step Sub-Systems. I had very similar problems-

Scheduled Packages
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html)
rupa

Your DTEXEC command looks wrong, as per the first error. /P for for supplying SQL security, see Books Online "..Allows the retrieval of a package that is protected by SQL Server Authentication..."

You have a file package not a SQL stored package, so /P cannot be used, and does not make sense. Maybe you want the "/De[crypt] password" option instead.
draxx
I'm running this from the server but as a domain admin and not the sql server agent account. Just some more background on this. This job actually deletes an Excel file on another server first. It then goes on to recreate the file with new data from our sql server db. I'll log in with the sqlserver agent account and try it.
Bad_Bob
Darren - Thanks for your time today. Everything is working now.
kritchai vanchainavin
You are really going to have to help me here, what is the error Use the job history or job step output log to start with.
Aknght
Darren - I execute this from the command line (dos prompt) and it worked

C:\Documents and Settings\administrator.SVC>dtexec.exe /FILE "F:\OASISDB_SSIS_PA
CKAGES\dts_CopyGREOORHS3.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORT
ING V /CONSOLELOG NCOSGXMT /De secretpassword
Seyfert
That error obviously does not give us much. Are you using the CmdExec job step type with DTEXEC as per the link The limited error makes me think you are still using the SSIS job step type. Use the former to get more information.
Raja Ratheesh
This is it

Date 6/1/2006 12:38:18 PM
Log Job History (gre)

Step ID 0
Server XXXX
Job Name gre
Step Name (Job outcome)
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed XXXX
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. The Job was invoked by Schedule 4 (test). The last step to run was step 1 (gre).

Date 6/1/2006 12:38:18 PM
Log Job History (gre)

Step ID 1
Server XXXXX
Job Name gre
Step Name gre
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: SVC\XXXX. The package execution failed. The step failed.
Ido F.

Where were you and who was the user For a realistic test you need to be logged onto the same server as runs SQL Agent, using the domain user account which you have set for the SQL Server agent service account. Is this the case

Do you know what I mean by the CmdExec job step type Why not try it Saying that logged onto the server with the service account user correctly is a very close simulation for testing as well.
SSIS LOGGING

Answer this question