Amour22015
asked on
SSIS 2005 - Send Mail Task, How to attach a variable FileAttachments
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.N ow.AddDays (-25))) & ".xls"
Dts.Variables("strFileName ").Value = strFileName
If System.IO.File.Exists(strF ileName) Then
System.IO.File.Delete(strF ileName)
End If
Please see FileAttachments.
Please see SendMailProperties to see if I am missing something.
Thanks
Thanks
FileAttachments.docx
SendMailProperties.docx
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
Dts.Variables("strFileName
If System.IO.File.Exists(strF
System.IO.File.Delete(strF
End If
Please see FileAttachments.
Please see SendMailProperties to see if I am missing something.
Thanks
Thanks
FileAttachments.docx
SendMailProperties.docx
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...
you have to go expression of file connection and change its expression.
Thanks...
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(strF ileName) Then
System.IO.File.Delete(strF ileName)
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
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(strF
System.IO.File.Delete(strF
End If
Is this what you mean?
The problem I am having:
Keeps attaching to:
d:\Jobs\AccountingMonthly\
Not to the updated xls file.
Should be attaching to variable:
d:\Jobs\AccountingMonthly\
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
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
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
Therefore adding the filename variable to the email messagesource should help.
HTH
Rainer
ASKER
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
I just ran the package and I received:
Content: File variable is - D:\Jobs\AccountingMonthly\
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
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
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
ASKER
Great that worked
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