I am trying to pass a global variable value into the
variable @RPT_BEGIN_DATE in an "Execute SQL" task. The
variable is part of the WHERE clause. The "Execute SQL"
task is as follows:

declare @RPT_BEGIN_DATE datetime

-- Generate report detail for failed jobs/steps

insert into T9801_EMAIL_JOBS_FAILED (EMAIL_LINE_TXT)

select substring(@@servername,1,11) +
substring(' ',len(@@servername),11) +
substring(j.name,1,43) +
substring
(' ',len
(j.name),43) +
substring(jh.step_name,1,33) +
substring(' ',len
(jh.step_name),33) +
-- Calculate fail datetime
CONVERT(CHAR(26),
dateadd(ss,cast(substring(cast(run_duration +
1000000 as char(7)),6,2) as int), -- Add Run
Duration Seconds
dateadd(mi,cast(substring(cast(run_duration +
1000000 as char(7)),4,2) as int), -- Add Run
Duration Minutes
dateadd(hh,cast(substring(cast(run_duration +
1000000 as char(7)),2,2) as int), -- Add Run
Duration Hours
dateadd(ss,cast(substring(cast(run_time + 1000000
as char(7)),6,2) as int), -- Add Start Time
Seconds
dateadd(mi,cast(substring(cast(run_time + 1000000
as char(7)),4,2) as int), -- Add Start Time
Minutes
dateadd(hh,cast(substring(cast(run_time + 1000000
as char(7)),2,2) as int), -- Add Start Time
Hours
convert(datetime,cast (run_date as char
(8))))))))),109)
from msdb..sysjobhistory jh join msdb..sysjobs j on
jh.job_id=j.job_id

where (getdate()
dateadd(ss,cast(substring(cast(run_duration +
1000000 as char(7)),6,2) as int), -- Add Run
Duration Seconds
dateadd(mi,cast(substring(cast(run_duration +
1000000 as char(7)),4,2) as int), -- Add Run
Duration Minutes
dateadd(hh,cast(substring(cast(run_duration +
1000000 as char(7)),2,2) as int), -- Add Run
Duration Hours
dateadd(ss,cast(substring(cast(run_time + 1000000
as char(7)),6,2) as int), -- Add Start
Time Seconds
dateadd(mi,cast(substring(cast(run_time + 1000000
as char(7)),4,2) as int), -- Add Start
Time Minutes
dateadd(hh,cast(substring(cast(run_time + 1000000
as char(7)),2,2) as int), -- Add Start
Time Hours
convert(datetime,cast (run_date as char
(8))))))))))
and (@RPT_BEGIN_DATE
<
-- Calculate fail datetime
dateadd(ss,cast(substring(cast(run_duration +
1000000 as char(7)),6,2) as int), -- Add Run
Duration Seconds
dateadd(mi,cast(substring(cast(run_duration +
1000000 as char(7)),4,2) as int), -- Add Run
Duration Minutes
dateadd(hh,cast(substring(cast(run_duration +
1000000 as char(7)),2,2) as int), -- Add Run
Duration Hours
dateadd(ss,cast(substring(cast(run_time + 1000000
as char(7)),6,2) as int), -- Add Start
Time Seconds
dateadd(mi,cast(substring(cast(run_time + 1000000
as char(7)),4,2) as int), -- Add Start
Time Minutes
dateadd(hh,cast(substring(cast(run_time + 1000000
as char(7)),2,2) as int), -- Add Start
Time Hours
convert(datetime,cast (run_date as char
(8))))))))))

and jh.run_status = 0

Thanks in advance for the help!
Scott Lindsey

You ready for some unnecessary hassle? I spent maybe a half a day with this problem

What you cannot do: create a sql statement with a ? as a placeholder for the parameter, then set the parameter

Here goes
0) I presume you have added the global variable ahead of time

1) add line (preferably at the top of the package
set @RPT_BEGIN_DATE = getdate(

2) Save the task

3) Open disconnected edit. Find the executesql task that you just created

4) Click on InputGlobalVariableNames. Click the Edit button

5) Add the name of your Global variable to the list (I think that it is a comma delimited list)

6) Save (Click OK

7) Click the SQLStatement lin

8) Find the line with "set @RPT_BEGIN_DATE = getdate()" replace getdate() with "?

set @RPT_BEGIN_DATE =

9) Sav

10) Save your package. It should run now

Russ

Russ,
That did the trick! Many thanks for your quick response!!
Scott