sql server dts: hi

I must send emails every month with dynamically named files as attachments.
The files are named according to the date on which they are generated.
For example on the first of November 2007, the file will be named myfile_1_11_2007.

I have created a variable called DynamicFileName with package scope, data type string and default value: d:\\tests\\

In "Send Mail Task Editor" Dialog Box, I have specified the following:

smtpConnection: smtptest.server.com
From :nemo@smtptest.server.com
To: nemo@smtptest.server.com
Subject: Dynamic File Email
MessageSourceType: Variable
MessageSource: blank
Priority: blank
Attachments: blank

In Expressions, I have specified:

FileAttachments: @[User::DynamicFileName] + "myfile_" + (DT_STR, 4, 1252) DAY( GETDATE() ) + "_" + (DT_STR, 4, 1252) MONTH( GETDATE() ) + "_" + (DT_STR, 4, 1252) YEAR ( GETDATE() ) + ".csv"

When I execute the package, I get the following errors:
-----------------------------------------------------------------------------------------
Error at Send Mail Task [Send Mail Task]: Either the file "d:\\tests\\myfile_1_7_2007.csv" does not exist or you do not have permissions to access the file.


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

Of course, the file does not exist. It will exist at tun-time. How can I tell the Send Mail Task to use a filename that is dynamic ?

By the way, once I have specified the code for FileAttachments, on trying to edit the Send Mail Task Properties, I can see that the Atachments field has been set to "d:\tests\myfile_1_7_2007.csv by itself: I never typed it there !! It seems that the task executes the code even before it is run. If I remove the attachment path manually, on running the dts, I get an error saying that "either the file does not exist or you do not have permission to access the file.


I would be most grateful if anyone could be of help

thanks



From http://www.developmentnow.com/g/103_2006_7_0_0_0/sqlserver-dts.htm

Posted via DevelopmentNow.com Groups

[quoted text, click to view]
) DAY( GETDATE() ) + "_" + (DT_STR, 4, 1252) MONTH( GETDATE() ) + "_=
" + (DT_STR, 4, 1252) YEAR ( GETDATE() ) + ".csv"
[quoted text, click to view]
ile_1_7_2007.csv" does not exist or you do not have permissions to access t=
he file.
[quoted text, click to view]
to edit the Send Mail Task Properties, I can see that the Atachments field=
has been set to "d:\tests\myfile_1_7_2007.csv by itself: I never typed it=
there !! It seems that the task executes the code even before it is run. I=
f I remove the attachment path manually, on running the dts, I get an error=
saying that "either the file does not exist or you do not have permission =
to access the file.
[quoted text, click to view]

Hi Nemo,

SSIS likes to validate everything before it executes, which can be a
problem when you are trying to do what you are doing here. I would
try one of the following 2 options:
1) Set the DelayValidation property for the SendMail task to TRUE.
This should prevent the validation taking place which should allow you
to deploy the package.
2) Create a dummy file. You would need to tweak your expression a bit
(probably using a debug variable) to get it to choose whether to run
using the dummy file or whether to use the generated filename.

Either way, when you execute the task you will get an error if the
file doesn't exist.

I would also suggest using SSIS's built in error handling to catch and
handle this problem - perhaps sending out an alternative email without
the attachment if the file does not exist.

Good Luck
J