I have no ActiveX skills, so I'm hoping this is going to be easy for those
that do.

I have a Global Variable (type =Date) in a DTS, which I want to set to the
current date (minus the time - ie convert(varchar,getdate,106) ) at the
beginning of the DTS.

How do I do it?!

Thanks. BM

And you want it in this format dd mon yy

I would assign this to a String datatype global variable

You can do this using DTS 2000 by using the ExecuteSQL task i.e.

SELECT convert(varchar(50),Getdate(),106) as DateRequired

The click the Parameters button and assign it to your Global Variable there.



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Beema" <none (AT) supplied (DOT) com> wrote

Hi I've two questions about this.

1. I added the ExecuteSQL Task, with the "select convert...", clicked on
Parameters and the list of Input Global Parameters was empty. The
GlobalVariable definitely exists though.

2. I am actually planning on using the GlobalVariable as a parameter in an
SQL statement. But the SQL Statement is in a transfer task - as far as I can
see, Global Variables are not usuable in this case are they?


The format "dd mon yyy" is not important, I am using the Global Variable as
a parameter in a Where clause, with the column being a datetime - with the
time part trimmed off.

Thanks,


----------------------------


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Drop down the combo box and you should find the GV. It is not an INput GV
it is an Putput GV.
Yes you can reuse the GV in a datapump. You would do

SELECT ............... FROM Table WHERE col = ?

Click on the parameters button and you can map the GV there.

Watch out for conversions

If you are reusing this in a datapump task then when move it to a GV can you
not do

SELECT ...... FROM Table WHERE col = CONVERT(varchar(50),Getdate(),106)



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Beema" <none (AT) supplied (DOT) com> wrote

It's OK to use GV's in a simple "select.. from... where col=?" But the
select statement in my datapump is a lot more complex.

I have declared local variables, and am assigning them based on the value of
the current date..

Here's a rough version of the SQL Statement I am using (the data goes to a
csv file btw)

---------------------
declare @FiscalPeriod varchar(30)
declare @Today datetime

select @Today = convert(varchar,getdate(), 106)

select @FiscalPeriod = FiscalPeriod
from Time_Dim
where Date_Time = @Today

select ColA,
ColB
datename(month, @Today) as Month
@FiscalPeriod,
etc...
from MyTable
inner join Time_Dim on MyTable.InsertDate = Time_Dim.DateTime
where Time_Dim.InsertDate = @Today
---------------------

the results of this query are sent to a CSV file for import into another
system.

My Dilemma is that I need to get the datename of the @Today value, and I'm
pretty sure that this goes beyond the capabilities of GV's. From what I've
read, they can only be used in a where clause.


I think that I am not going to be able to use GV's in my case.








"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

declare @FiscalPeriod varchar(30)
declare @Today datetime

select @Today = convert(varchar,getdate(), 106)



select ColA,
ColB
datename(month, @Today) as Month,
(select FiscalPeriod from Time_Dim where Date_Time =
convert(varchar,getdate(), 106)) as FiscalPeriod ,
etc...
from MyTable
inner join Time_Dim on MyTable.InsertDate = Time_Dim.DateTime
where Time_Dim.InsertDate = convert(varchar,getdate(), 106)



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Beema" <none (AT) supplied (DOT) com> wrote

Can you not rework the statement to

select ColA,
ColB
datename(month, Getdate()) as Month,
FiscalPeriod as FiscalPeriod ,
etc...
from
MyTable inner join Time_Dim
on MyTable.InsertDate = Time_Dim.DateTime
where
Time_Dim.InsertDate = convert(varchar,getdate(), 106)




--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Beema" <none (AT) supplied (DOT) com> wrote

Yes, however I have 15 of these in various datapumps. And periodically I
need to use a past date, instead of getdate()

Thats' the reason I'm trying to set this date using a GV, as I only have to
change the date in one place, rather than editing each of the SQL
Statements.


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Ahh

OK then

If the ExecuteSQL task and further on the DataPump task is failing to parse
and not doing what you want then this can be a problem. It usually a
problem when the ? is in a subquery but this can be worked around by
buliding a statement with the same metadata and plugging the GVs into that

from what I see though you would quite like to do

CONVERT(varchar(50),?,106)

Correct?

Personally I would build the statement in an Active Script task and then
apply it to the appropiate task. This way you will assign it in only only
place as you require.

Global Variables and SQL statements in DTS
(http://www.sqldts.com/Default.aspx?205)


--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Beema" <none (AT) supplied (DOT) com> wrote