Use GlobalVariables in Transform Data 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 2000 Forums

Import/Export (DTS) and Replication (2000)

Use GlobalVariables in Transform Data Task

Author

Topic

william_lee78
Yak Posting Veteran

53 Posts

Posted - 2004-09-29 : 06:21:00

Hi,

May I know is it possible to use Globalvaribles in SQL query under Transform Data Task? Basically, I define a DTSGlobalVariable("CDLabel") and I want to use it in SQL query like this:

SELECT DTSGlobalVariable("CDLabel"), Name, Year
FROM Music

But, this is incorrect. Can someone teach me on how to retrieve DTSGlobalVariable value in SQL query under Transform Data Task?

Thank you!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-29 : 07:45:47

The implementation of parameters is a bit flaky.
The best way I've found round this is to set up your source query with a placeholder to allow you to create the transformations, i.e.

SELECT 'blah' AS CDLabel, Name, Year
FROM Music

Then create an ActiveX task that dynamically sets the SQLStatement property of the transform task, as follows:


Function Main()
	Dim objPkg
	Dim objTask
	Dim objTranform
	Dim strSQL
	
'Create objects
	Set objPkg = DTSGlobalVariables.Parent
	Set objTask = objPkg.Tasks("DTSTask_DTSDataPumpTask_1")
	Set objTransform = objTask.CustomTask

'Create SQL String
	strSQL =  "SELECT " & CStr(DTSGlobalVariables("CDLabel"))  & " AS CDLabel, Name, Year"
	strSQL = strSQL & "FROM Music"

'Set custom task property
	objTransform.SourceSQLStatement =strSQL
	
'Destroy objects
	Set objTransform = Nothing
	Set objTask = Nothing
	Set objPkg = Nothing

'Report success
	Main = DTSTaskExecResult_Success
End Function

Set the workflow such that this runs before the transform and you should be done.

Mark

william_lee78
Yak Posting Veteran

53 Posts

Posted - 2004-09-29 : 23:46:59

Hi Mark,

I try out your suggestion. But, I got an error. The error is "Microsoft Data Transformation Services (DTS) Package Task "Excel" was not found". "Excel" is my Transform Data Task.

By the way, how can I find out my transform data task name??

Thank you.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-30 : 07:46:20

Hi William,
Either:
1. right-click the task, select "workflow properties" and get the name from the "Options" tab of the resulting dialog

2. Select "Disconnected Edit" from the "Package" menu and expand the "Tasks" node of the tree on the left.

Chances are, if you've only the one transform in the package, it will be named as per my example.

Mark

william_lee78
Yak Posting Veteran

53 Posts

Posted - 2004-10-03 : 21:05:38

Hi Mark,

Thank you so much. I got the thing works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources