locked
Getting validation errors from Send Mail with attachment RRS feed

  • Question

  • I have a simple SSIS package with two  task.  The Control flow loads a flat file  to a SQL table, and the send mail task  executes only if the file load fails. 

    The send mail task attaches the package log file to the email.  I am able to test each task independantly in BIDS and get the desired results.  When I execute the package thru BIDS I get the error listed below and the package never starts.  The logfile is there from previous task executions, and I have permissions. I receive the email and attachment when I run the task seperately. 

    TITLE: Package Validation Error
    ------------------------------

    Package Validation Error

    ------------------------------
    ADDITIONAL INFORMATION:

    Error at Send Mail Task - xxxxx LOAD FAILED [Send Mail Task]: Either the file "R:\SSIS\PKGlogs\xxxxx.log" does not exist or you do not have permissions to access the file.

    Error at Send Mail Task - xxxxx LOAD FAILED: There were errors during task validation.

     (Microsoft.DataTransformationServices.VsIntegration)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

     

     

    Wednesday, January 5, 2011 4:24 PM

Answers

  • OK, Then separate the two tasks. Remove the Send Mail task from the Data Flow package. Have that just do the Data Flow and what ever Control flow items it need to support it. (BTW, can you not do some kind of validation with a scipt task or file system task BEFORE the data flow to see if the file is even there, then send an email to that effect if its missing?)

    Set up a second packag that calls the first one with an Execute Package Task on the Control Flow. If THAT task fails, then send the log file from THAT task. You will need a Precedence Constraint set to Failure between the two tasks. Hopefully, the log file from the first package will be free for use by the time the Send Mail task wants to attach it.

    Hope this helps.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Wednesday, January 5, 2011 5:02 PM
  • break the process into two SSIS packages, the first writes the log and the second sends it.  Create a job that calls the two packages.  The log file should be closed at the completion of the first package.  You could also log the information to a table and then your send mail step just queries the log.

     

    Wednesday, January 5, 2011 5:04 PM

All replies

  • You can't access the file to make it into an attachment because it is currently in use by the very package that is still writing to it. No amount of Delay Validation is going to help either.

    Just curious, why send the log file? Why not attach a file that contains rejected records from the data flow?


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Wednesday, January 5, 2011 4:32 PM
  • In this case the input file does not exist.  In a production environment we do an all or nothing load and the developer needs the log to trouble shoot the issue.

    An other suggestion on how I could get around this.

    Wednesday, January 5, 2011 4:56 PM
  • OK, Then separate the two tasks. Remove the Send Mail task from the Data Flow package. Have that just do the Data Flow and what ever Control flow items it need to support it. (BTW, can you not do some kind of validation with a scipt task or file system task BEFORE the data flow to see if the file is even there, then send an email to that effect if its missing?)

    Set up a second packag that calls the first one with an Execute Package Task on the Control Flow. If THAT task fails, then send the log file from THAT task. You will need a Precedence Constraint set to Failure between the two tasks. Hopefully, the log file from the first package will be free for use by the time the Send Mail task wants to attach it.

    Hope this helps.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Wednesday, January 5, 2011 5:02 PM
  • break the process into two SSIS packages, the first writes the log and the second sends it.  Create a job that calls the two packages.  The log file should be closed at the completion of the first package.  You could also log the information to a table and then your send mail step just queries the log.

     

    Wednesday, January 5, 2011 5:04 PM
  • Steve,

    Your suggestion promted a simple solution.  I will create a link, or include the path to the logfile in the email and grant the developer read only access to it.  

    Wednesday, January 5, 2011 5:15 PM
  • In our production environment, the scheduler never starts the package unless the input file is on the server. I've never called a package from another I will try this as a test. 

    Thanks!

    Wednesday, January 5, 2011 5:20 PM