?
Solved

SSIS 2005 - Send Mail Task, How to attach a variable FileAttachments

Posted on 2012-08-23
9
Medium Priority
?
5,326 Views
Last Modified: 2012-08-24
Hi and thanks,

I have a SSIS 2005 package that when finished it sends out a email message using "Send Mail Task" this part works fine.

I am trying to pass variable to fileattachment in sendmail task.This task is working fine if I pass the file path and file name, I am getting email with file attachment, But I want this task to pick file with different name (the variable), file path and file extension are same all the time. How do I set this up in the variable, so job will run and should pick up new file?


I have read this:
Create a variable. (done)
Edit the Send Mail Task, under the Expressions, open the Property Expression Editor, set the variable to FileAttachments Property (this part is done).

I Don't understand the rest?
Create a Package Configuration, in the Package Configuration Wizard, select the value of the variable(it will appear in the configuration file).
Deploy the package. Whenever needed, just change the value in the configuration file.


I just want the attachment to pick up the variable.

I have this in the Script Task before the Send Mail:
 Dim strFileName As String
        strFileName = "D:\Jobs\AccountingMonthly\" & Year(DateTime.Now.AddDays(-25)) & "\" & MonthName(Month(DateTime.Now.AddDays(-25))) & ".xls"
        Dts.Variables("strFileName").Value = strFileName
        If System.IO.File.Exists(strFileName) Then
            System.IO.File.Delete(strFileName)
        End If

Please see FileAttachments.

Please see SendMailProperties to see if I am missing something.

Thanks


Thanks
FileAttachments.docx
SendMailProperties.docx
0
Comment
Question by:Amour22015
  • 4
  • 4
9 Comments
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 38328227
Hi,
sorry - your attachment does not show the send email properties.

I am not sure but your script DELETEs the file - so what file should then be sent?

Attached a small working sample. I create a dummy text file which is sent. Just remove the .txt file extension and set your parameters in the SMTP Connection Manager and in the Send Email task.

HTH
Rainer
EE-SampleSendEmail.dtsx.txt
0
 
LVL 14

Expert Comment

by:nishant joshi
ID: 38328248
you can not changed expression in send email task for variable attachments.
you have to go expression of file connection and change its expression.

Thanks...
0
 

Author Comment

by:Amour22015
ID: 38329137
Hi,

nishantcomp2512

Can you give detail on what you mentioned?  I am new to SSIS and don't understand? Please step-by-step with snap shots?

Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 2000 total points
ID: 38329234
Hi,
nishantcomp2512 meant that if you have a file connection manager this one can not be set to a variable.

Have you tried my package?
Have you confirmed that the file exists on the hard disk in the moment the email is sent?
You may change the "MessageSource" to an expression to output the file variable.

I have also changed your script code (added a ToString on the year which is of type int):
strFileName = "D:\RainerJ\EE\SendEmail\" & Year(DateTime.Now.AddDays(-25)).ToString() & "\" & MonthName(Month(DateTime.Now.AddDays(-25))) & ".xls"

Here the sample package screenshot:
Send Email Package Sample
HTH
Rainer
0
 

Author Comment

by:Amour22015
ID: 38329347
Rainer,

You mentioned:
I am not sure but your script DELETEs the file - so what file should then be sent?

So I edited out:
If System.IO.File.Exists(strFileName) Then
            System.IO.File.Delete(strFileName)
        End If

Is this what you mean?

The problem I am having:
Keeps attaching to:
d:\Jobs\AccountingMonthly\2006\July.xls

Not to the updated xls file.

Should be attaching to variable:
d:\Jobs\AccountingMonthly\2012\July.xls

You said:
Have you tried my package?

I opened it but it was a txt file and I did not know what to do with it.

You said:
Have you confirmed that the file exists on the hard disk in the moment the email is sent?

don't know how to?
I noticed it is there after the package finishes.

You said:
You may change the "MessageSource" to an expression to output the file variable

You mean do what is on the snap shot?
I will try.

Thanks
0
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 38329525
Hi,

Sample package:
EE does not support uploading DTSX files hence I just added a ".txt" extension to the package so that I was able to upload it.
After downloading the sample package just remove the .txt and you will have the
EE-SampleSendEmail.dtsx

MessageSource:
Yes, because then you can verify if the variable with the file name is updated correctly.

Delete Question:
I could not see your whole package flow in the attached documents therefore I am not sure.
If you run the following sequence:
1. Script Task - Create file name, pass to variable, delete file if file exists
2. Data flow to create the file
3. Send Email task
then the delete script should be fine.

My suggestion:
As you said that the email task is always attaching "d:\Jobs\AccountingMonthly\2006\July.xls" then I would assume that either the variable is not set correctly in the script task or that the script is generating a wrong file name due to having wrong machine time.
Therefore adding the filename variable to the email messagesource should help.

HTH
Rainer
0
 

Author Comment

by:Amour22015
ID: 38329583
Rainer,

I just ran the package and I received:
Content: File variable is - D:\Jobs\AccountingMonthly\2012\July.xls-

Also noticed that the correct variable was attached.

must have been this statement that was added:
.ToString()

But:
I also noticed that it is creating 2 xls files:
1.) June
2.) July

noticed that the data is ending up in June, which needs to be in July.
but maybe I should repost this question?

let me know?

Thanks
0
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 38329634
Hi,
thanks - yes please repost because that is another question.
In your question add as much information about how you create your file as possible.

Thanks again and KR
Rainer
0
 

Author Closing Comment

by:Amour22015
ID: 38329670
Great that worked
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question