Global Variables and SELECT Statement

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 2000 Forums

Import/Export (DTS) and Replication (2000)

Global Variables and SELECT Statement

Author

Topic

squab
Starting Member

15 Posts

Posted - 2004-08-06 : 10:57:30

Hi,

I have a request with this header :
"SELECT TransactionName, @MM+@YY AS [Date], ThresholdTime, AVG(ElapsedTime) AS MeanResponseTime ..."

with @MM and @YY, two global variables.
@MM : Month on 2 digits (eg : '08')
@YY : Year on 2 digits (eg : '04')

In my request, I need a column with '0804' constant string on each row (for example).

I need to include this request in a DTS Package and my two variables must be define as Global Variables. So, in my package, I must remplace "@MM+@YY" by "?+?" in order to choose the global parameters. But it doesn't work. A "Syntax Error" occured when I click on "Parse Query button".

Anybody has an idea ?

Best Regards,

Fred

squab
Starting Member

15 Posts

Posted - 2004-08-09 : 05:02:23

Hi all,

Sorry, but I'm locking, really :-( . Anybody could help me please ? If my message is not understanding, I can explain again. Sorry for my bad english.

Thanks in advance.

Regards,

Fred

schuhtl
Posting Yak Master

102 Posts

Posted - 2004-08-09 : 10:52:27

Try the following:
1. Create package global variable gv_SQL
2. Create 3 new Tasks: 1 ActiveX, Dynamic Property, Execute SQL (add the following sql statement - SELECT NULL)
3. Create On Success precedence from ActiveX Task to Dynamic Property Task and from the Dynamic Property Task to the Execute SQL task.
4. Insert logic to build the dynamic query in the ActiveX task, my example:
Function Main()
Dim sMonth, sYear

'insert month logic here
If len(trim(Cstr(datepart("m", now() ) ) ) ) < 2 then
sMonth = "0" + trim(Cstr(datepart("m", now())))
else
sMonth = trim(Cstr(datepart("m", now())))
end if
'insert year logic here
sYear = right(Cstr(datepart("yyyy", now())),2)

DTSGlobalVariables("gv_SQL") = "SELECT TransactionName, " & sMonth + sYear & " AS [Date], ThresholdTime, AVG(ElapsedTime) AS MeanResponseTime From TEST_TABLE"
Main = DTSTaskExecResult_Success
End Function

5. Double click DYnamic Property Task, Click Add Button, Expand where it says Tasks on the left, find the Execute SQL task added in step 2(above), Find the property SQL Statement and then double click default value, under Source select Global Variable, Then select gv_SQL from the Variable dropdown listbox.

There might be an easier way to do this but this should work.

squab
Starting Member

15 Posts

Posted - 2004-08-09 : 12:37:28

Hi schuhtl

Many Thanks to you ! it works ! You save me !

Best Regards,

Fred

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources