How to use variable in send mail task

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

How to use variable in send mail task

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2011-12-20 : 11:09:42

Hi,
I made a SSIS project which is running fine.
It has 4 steps.

1) File System Task.
2) Execute SQL task.
3) Data Flow Task
4) Send mail task.

I am running a sp in execute sql task.
Its fetching 3 dates as :-

Declare @dt1 datetime,
@dt2 datetime,
@dt3 datetime

set @dt1 = dateadd(d,-7,GETDATE())
set @dt2 = dateadd(d,-1,GETDATE())
set @dt3 = dateadd(d,-8,GETDATE())

Its using @dt1 and @dt2 to fetch data from other tables.

I want this @dt1 and @dt2 in my send mail task...
beacuse when it sends mail to other people, they should know which dates IS IT FOR...

I wanted to put it in the message source type - variable
and then message source - The data is between @dt1 and @dt2...

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 12:03:09

create two variables in package of datetime type. Use the above expressions in execute sql task like below


SELECT dateadd(d,-7,GETDATE()) AS dt1,dateadd(d,-1,GETDATE()) as dt2

then assign dt1 and dt2 to variables created

now add a third variable of type string and set evaluateasexpression property to true and set expressions as

="The data is between " + (DT_WSTR,20)@[User::Variable1] + " and " + (DT_WSTR,20)@[User::Variable2]

then use this variable for setting the message source in sent mail task.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2011-12-20 : 12:29:38

I get a error when I set the expression as
The data is between....

Error:-
A truncation has occured during evaluation of hte expression.

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 12:58:06

="The data is between " + (DT_WSTR,30)@[User::Variable1] + " and " + (DT_WSTR,30)@[User::Variable2]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2011-12-20 : 13:27:17

Now , the error is in execute sql task
It shows

[Execute SQL Task] Error: Executing the query "execute rtw_Weekly_Dept_Sales_Margin; SELECT dateadd(d,-7,GETDATE()) AS dt1,dateadd(d,-1,GETDATE()) as dt2"

failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What should I put in the paramter mapping in execute sql task?

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-21 : 11:50:26

put it a separate execute sql task. dont put multiple statements with varying resultsets inside same sql task

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2011-12-21 : 14:46:23

Now working perfectly,

Thanks a lot visakh

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 04:22:14

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-01-25 : 11:14:06

@ visakh

I copied the package (.dtsx) and changed some tsql codes in the package and also the variable (new dates).

Now iam getting the error 0XC0010001
Failed to lock variable" the data is between 1/18/2012 5:45:09 and 1/25/2012 4:39:45 for read access with error 0XC0010001" the variable cannot be found. the variable may have been changed or variable not being created.

How to solve this.

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-25 : 11:52:12

where this error is coming? are you trying to use variable inside script task?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-01-25 : 12:08:59

quote:
Originally posted by visakh16

where this error is coming? are you trying to use variable inside script task?

This error is coming in last step that is Send mail task.
I have
1) File system task
2) execute sql task (the 2 time variables,dt3,dt7 are declared here)
3) 5 data flow tasks.
4) send mail task (i am declaring another variable in message source using the 2 time variables in step 2) .

That is execvaluevariable
the value of expression is :-
"The data is between " + substring((DT_WSTR,30)@[User::dt7] ,1,9)+ " and " + substring((DT_WSTR,30)@[User::dt3],1,9)

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-25 : 16:57:14

are both the variables in package scope? also are you sure you're using correct names and mapping them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-01-26 : 09:00:58

Yes all three are in package scope.
Yes i am mapping them right.

dt3 and dt7 (2 variables, datetime both) in execute sql task (resultset)
and then d (3rd variable as a string put in message source as expression
"The data is between " + substring((DT_WSTR,20)@[User::dt7],1,9) + " and " + substring((DT_WSTR,20)@[User::dt3],1,9)

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-26 : 09:03:13

quote:
Originally posted by skybvi

Yes all three are in package scope.
Yes i am mapping them right.

dt3 and dt7 (2 variables, datetime both) in execute sql task (resultset)
and then d (3rd variable as a string put in message source as expression
"The data is between " + substring((DT_WSTR,20)@[User::dt7],1,9) + " and " + substring((DT_WSTR,20)@[User::dt3],1,9)

Regards,
Sushant
DBA
Virgin Islands(U.K)

first try out putting variable alone in expression and printing it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-01-26 : 10:07:39

Hi,
I put just 2 steps, execute sql task and send mail task.

1) Execute sql task contains:-
dt3,dt7 variables (in resultset)

Code :-
Begin
Declare
@dt3 datetime,
@dt7 datetime

SET @dt3 = (getdate()-1)
set @dt7 = dateadd(d,-7,GETDATE())
Select
(getdate()-1) as dt3,
dateadd(d,-7,GETDATE()) as dt7
END

2)send mail task:-

m as variable
expression
"The data is between " + substring((DT_WSTR,30)@[User::dt7],1,9) + " and " + substring((DT_WSTR,30)@[User::dt3],1,9)

Then also , I am getting error:-

Error: Failed to lock variable "The data is between 1/19/2012 and 1/25/2012" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

other errors whcih are coming with that are :-

Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.

Error: The type of the value being assigned to variable "User::m" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-26 : 10:52:50

have you declared variable m? also if you're directly setting value inside send mail task why do you need variable m?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-01-26 : 11:07:03

@visakh,

yes, on the left side of screen(vaiables scrren), I can see 3 variables declared ( dt3,dt7 and m)

So where i am wrong. shld i change in expression --> user::dt3 to just dt3 and similarliy dt7.

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-26 : 13:28:42

ok...
I think you dont need variable m at all..
You just need to set an expression for message property of send mail task in its expression builder and put expression that uses the variable dt3 and dt7

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2012-01-26 : 15:58:40

quote:
Originally posted by visakh16

ok...
I think you dont need variable m at all..
You just need to set an expression for message property of send mail task in its expression builder and put expression that uses the variable dt3 and dt7

ok, I will do the change and let you know.
Thanks a lot for helping.

Regards,
Sushant
DBA
Virgin Islands(U.K)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-26 : 16:27:12

welcome
let me know if you face any more issues

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources