How to pass text in configuration file with a call to parameter

Question

I have a package that sends emails, I have it set with a configuration file so I can pass a parameter with the addresses, attachments, etc. that I want to use, one of these parameters is the Message_source:

in which I pass the content of the email.

my problem comes in that I want to pass formatted text and a dynamic field (date). how can I do this so I can send something like this:

   <Configuration ConfiguredType="Property" Path="\Package.Variables[User::Email_Content].Properties[Value]" ValueType="String">
        <ConfiguredValue>here I want to pass a date, here is the date:  @Date</ConfiguredValue>
    </Configuration>

so I can generate something like this:

here I want to pass a date, 

here is the date: 05-01-2023

I know that I can parse the date with:

RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2) + "-" +  (DT_STR, 4, 1252) DATEPART("yy" , GETDATE())

however adding this to the config file right away doesn't work.

score 0 · Answer 1 · answered Jan 05 '23 at 16:23

If it's going to work, I think you're going to add in a few more levels of indirection.

What you want is to have an Expression as the content of a variable. It's happy to assign your string to the Value property but what is being sent is not the evaluated @Date but the literal characters @ D A T E

<Configuration ConfiguredType="Property"
 Path="\Package.Variables[User::Email_Content].Properties[Expression]" ValueType="String">
<ConfiguredValue>"I want to pass a date, here is the date: " + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2) + "-" +  (DT_STR, 4, 1252) DATEPART("yy" , GETDATE())</ConfiguredValue>
</Configuration>

The same rules will apply with string building and all that so if you want to have it look like "I want to pass a date, here is the date: @Date" then you need to build out a valid expression. I'd create a Variable named Test and plug away until I get the syntax correct and then slap that, carefully, into your XML/dtsconfig

"I want to pass a date, here is the date: " 
+ RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2) 
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2) 
+ "-" +  (DT_STR, 4, 1252) DATEPART("yy" , GETDATE())

Finally, if that isn't working then ensure that @[User::Email_Content] has the EvaluateAsExpression property set to True

How to pass text in configuration file with a call to parameter

1 Answers1