June 19, 2009 at 4:43 pm
Hi I have been asked to provide a solution to automate email to suppliers. I have been following a great article by Allan Mitchell:
http://www.sqlis.com/post/Shredding-a-Recordset.aspx
The following expression evaluates without error:
"Please confirm Purchase Order " + @[User::PurchasedOrderId]
When I expand as follows:
"Please confirm Purchase Order " + @[User::PurchasedOrderId] +"will be delivered by" + @[User::DueDate]
I receive error (see attached).
Can anyone help on this.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 19, 2009 at 6:43 pm
It looks like your DueDate variable is a Date and you are using it as if it were a String. To use it as you wish, you need to cast it as a string in your expression.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 20, 2009 at 3:10 am
Hi Phil, I think that i am on the right track. I now have;
"Please confirm Purchase Order " + @[User::PurchasedOrderId] + "will be delivered on " + (DT_WSTR, 50) (DT_DBTIMESTAMP) @[User::DueDate]
which evaluates as attached.
The evaluated value does not reference a value returned by the variable @[User::PurhaseOrderId] (this may be quite correct) but as I have never written an expression previously I am unsure. Also it would be nice if the date read 19/06/2009 (uk format).
I also note within the control flow 'Foreach Loop Container' a red x for the 'Send Mail Task' with message 'No recipient is specified' (attached screen dump). I have also attached screen dump of the variables defined within the package.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 20, 2009 at 3:35 am
No problem. If you can live without the leading zeros, this will get you there, I think:
"Please confirm Purchase Order " + @[User::PurchasedOrderId] + "will be delivered on " + (DT_WSTR,2)DAY(@[User::DueDate]) + "/" + (DT_WSTR,2)MONTH(@[User::DueDate]) + "/" + (DT_WSTR,4)YEAR(@[User::DueDate])
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 20, 2009 at 3:40 am
In your send mail task, you need to create an Expression (use the Expressions section in the task editor) for the ToLine property and set that to your EmailAddress variable.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 20, 2009 at 3:43 am
Phil thanks for that, just what I wanted. I was just reviewing a thread by greengiant;
"Please confirm Purchase Order " + @[User::PurchasedOrderId] + "will be delivered on " + RIGHT("00" + (DT_WSTR, 10) DATEPART( "mm", (DT_DBDATE) @[User::DueDate] ) ,2) + "/" + RIGHT("00" + (DT_WSTR, 10) DATEPART( "dd", (DT_DBDATE) @[User::DueDate] ) ,2) + "/" + (DT_WSTR, 10) DATEPART( "yyyy", (DT_DBDATE) @[User::DueDate] )
Your resolution gave the format required, the above was US date format.
Phil will the expressions only be populated with the variable values when the package is executed? As the 'Send Mail Task' is currently displaying 'No Recipient Specified'? (I am unable to test from home) no mail server. I have attched screen dump of my 'Send Mail Task'.
Thanks,
Phil.
Edit:
Added a second screen dump of the 'Send Mail Task v2'. The text within my expressions is pulled through.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 20, 2009 at 4:33 am
I read a few articles and have now set the 'Delay Validation' of the 'Send Mail Task' to 'True'. The error message has now dissapeared.
Now I need to import package into SSMS then test.
Thanks for your help.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 20, 2009 at 4:44 am
Good work Phil.
Way too many Phils in this thread, so this Phil is signing off
Phil 😉
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 21, 2009 at 4:04 am
Thanks Phil
🙂
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 22, 2009 at 3:09 am
Just to update this post I found the following article regards 'Custom Logging using Event Handlers' that others may find of use.
Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply