none
Mailing multiple attachments problem

    Dotaz

  • I created SSIS package that runs several reports and exports the files (Excel and PDFs) to a shared folder. Then it attaches these files using the Mail task and emails them to an email distribution group. 

    This was working fine until I made some changes in the source database (additions and changes to tables, views, and stored procedures - which add new functionality).  Now 2 of the 6 files are missing in the email that is sent out (the missing files happen to be PDF files, the 4 files in the email are Excel files.). But all the files (all 6 of them) are in the shared folder.  Only one of the changes, as far as I can tell, affected one of the reports and I changed that one back.

    The package, that runs the reports, creates the PDF files first.  All the reports are run in a sequence and then the mail task runs in the order below.

    Create shared folder -> Run Report1 -> Run Report2 -> Run Report3 -> Run Report4 -> Run Report5 -> Run Report6 -> Run Mail task

    I use an expression in the Mail task to populate the Attachment field.  I have 6 variables containing the report names, 1 for the path, and one for the concatenated report names and path.

    No changes were made to the reports or the package.  I am puzzled why the PDF files are no longer included in the email.

    Any ideas?


    Fred Schmid

    čtvrtek 21. září 2017 18:37

Odpovědi

  • Sorry for not replying sooner.  I found the problem to be not including a path variable in the Package Configuration.  After I did that the package started working again.  What I didn't mention before is that the path variable changed for this new SSIS package.  Since I copied the package from an existing package.  The new package used the default value for the path variable.  I did assign a new value to the path variable but I guess it doesn't look at that until after it errors out.  Oh well, it is fixed now.

    Fred Schmid

    • Označen jako odpověď MES_Analyst čtvrtek 12. července 2018 13:05
    čtvrtek 12. července 2018 13:05

Všechny reakce

  • Hi Fred,

    SSIS persists your database metadata at the development time (but not the data) so every time changes are made in the database SSIS needs to be QAed.

    Without knowing the intimate details about your package it is not possible to troubleshoot.

    I assume/hope you have enough logging put in place to scout and see any errors, I bet there are.

    And likely even worst something is done to ignore them and allow the package to run to completion.

    The rule of thumb in IT is to fail fast and to fall hard.


    Arthur

    MyBlog


    Twitter

    čtvrtek 21. září 2017 18:42
    Moderátor
  • Hi Arthur,

    Thanks for responding.

    The changes were made in the database that the SSRS reports use.  All the reports work with no errors.

    The package calls the reports and exports files to a shared folder.  All the report exported files are in the shared folder.  None of them are missing.

    The problem lies with the Mail Task.  It is trying to attach the files in the folder.  It does attach 4 out of the 6 files.

    The SSIS package and the SSRS reports run on a different database server than the source database for the reports.  Nothing was changed with the reports and the packages.

    The only thing I can guess at what is happening is the Attachment variable is getting truncated somehow, cutting off the last two report file names.

    There are no errors with the SSIS package.  It does send the email out but with only 4 of the 6 files.  The log only shows the package starting and ending.


    Fred Schmid

    čtvrtek 21. září 2017 20:56
  • You are welcome Fred,

    you need to log the value of the attachment file variable to confirm

     


    Arthur

    MyBlog


    Twitter

    pátek 22. září 2017 21:26
    Moderátor
  • Currently, I am logging to SSIS log provider for SQL Server.  Is there a simple way of logging the variable to that?

    Fred Schmid

    úterý 26. září 2017 13:01
  • Nah, that one logs to sysssislog 

    You need a different log provider that can be customized to capture the 

    OnVariableValueChanged

    event which can log to a SQL table.


    Arthur

    MyBlog


    Twitter

    úterý 26. září 2017 14:40
    Moderátor
  • Sorry for not replying sooner.  I found the problem to be not including a path variable in the Package Configuration.  After I did that the package started working again.  What I didn't mention before is that the path variable changed for this new SSIS package.  Since I copied the package from an existing package.  The new package used the default value for the path variable.  I did assign a new value to the path variable but I guess it doesn't look at that until after it errors out.  Oh well, it is fixed now.

    Fred Schmid

    • Označen jako odpověď MES_Analyst čtvrtek 12. července 2018 13:05
    čtvrtek 12. července 2018 13:05