SSIS LOGGING

SSIS LOGGING

• RobAtCDW

  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

  
  

• Icsoft

  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

  
  

• Webbert

  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.
  

• Deborah Lukenbill

  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.

  
  
  

• neilprivate

  Darren - Thanks for your time today. Everything is working now.
  

• OmkarRex

  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.

  
  

• B. Fuddled

  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.
  
  

• Ilan Levy

  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.
  
  

• Attiks

  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)
  

  
  
  

• Hafiz Basharat

  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.

  
  

• Leif Eklund

  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