Hi all,
Is there a way to schedule a DTS pacakge to run on the 3rd working day of
every month?

TIA,
Joe

A possible solution is to use an Active-X task as a first step in your DTS
package

Within this task:
Use SET DATEFIRST to set the weekday.
Using GETDATE and the defined weekdays to test for the 3rd work day of
the month
Use workflow to decide whether to continue running the remaining steps.

Set the DTS job to run every day


"jaylou" wrote:

Thank you.
This a very good idea.

Your first task in the package could check to see if today is the 3rd day
and if its not, ensure the package doesn't continue by affecting the
precedence constraints.

That's probably the best way of doing it I think.

Regards
Jamie Thomson
An SSIS blog - http://blogs.conchango.com/jamiethom...tegory/71.aspx


"jaylou" <jaylou (AT) discussions (DOT) microsoft.com> wrote

Thanks for the response, but now for the real question.

How do I determine what day of the week the first day of a month is? for
examdple If 3/1/05 is a Saturday, the 3rd working day would be the 5th.
I was trying to to use @@DATEFIRST, but I dont know how to set it to the
first day of the month.

Thanks again,
Joe

jaylou wrote:
Use DatePart in an ActiveX script.

http://msdn.microsoft.com/library/de...ctdatepart.asp
--
To Email Me, ROT13 My Shown Email Address

In message <nKJ_d.84897$y25.71995 (AT) fe3 (DOT) news.blueyonder.co.uk>, Jamie
Thomson <jamiekthomson (AT) removethisbit (DOT) blueyonder.co.uk> writes
This may be what you mean by "affecting the precedence constraints", but
the simplest way to stop execution is to use a workflow script on a
step, and return DTSStepScriptResult_DontExecuteTask. There are
alternatives around disabling steps or changing them precedence
constraints themselves, but they get very messy real quickly and are
harder to maintain as they require you to hard code object names.

Simple example of using workflow for this purpose -

How can I check if a file exists?
(http://www.sqldts.com/default.aspx?211)


Whilst pure date functions will get you the 3rd day of the working week
based on a Monday to Friday pattern, you probably still need to cope
with public holidays. For this I suggest a simple table with these dates
in. I use such a table for all sorts of things, although I originally
built it so that I did not have to re-work schedules and alert routines
for public holidays, and then change them back the next week. For UK
related systems this is a very useful page-

DTI Employment Relations - UK Public and Bank Holidays
(http://www.dti.gov.uk/er/bankhol.htm)


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Thanks all for the responses!
Here is how I set up my proc. I changed my clock on my computer and
checked it against every month this year and it worked for the 3rd working
day.
I dont know if it's the most efficient way but it works.
Thanks again.

declare @month varchar(12),@year varchar(4), @date varchar(12),@today
Varchar(12),@day varchar(2),@dayOfWeek int,@todayDay int
SELECT @day = day(getdate())
select @month = month(getdate())
select @year = year(getdate())
Select @date = @month+'/'+'1'+'/'+@year
select @today = @month+'/'+@day+'/'+@year
select @dayOfWeek = (select DATEPART(dw, @date))
select @todayDay = day(@today)
select @dayOfWeek
select @todayDay

if (@dayOfWeek = 7 and @todayDay = 5)
begin
print 'copy table'
end
else
if @dayOfWeek = 1 and @todayDay = 4
begin
print 'copy table'
end
else
if @dayOfWeek in (2,3,4) and @todayDay = 3
begin
print 'copy table'
end
else
if @dayOfWeek in (5,6) and @todayDay = 5
begin
print 'copy table'
end
else
begin
print 'not today'
end