Trouble setting DTS Dynamic input global variable

Question

Post reply

Trouble setting DTS Dynamic input global variable

Mark Bosomworth

Say Hey Kid

Points: 669
More actions
April 28, 2005 at 9:12 am

#65802

I am trying to dynamically set the input parameter global variable for a SQL execute task. However first of all my SQL execute task will not work with the like statement. My SQL is:
Select TableName from ServerSyncControlTable
where TableName like ?
Parameter1 global variable = tablename_%
I have mapped an input global variable to parameter1 and given this a value of tablename_%. When this runs it executes OK but does not return any data. If I hardcode the value it work fine ie:
Select TableName from ServerSyncControlTable
where TableName = ?
Parameter1 global variable = tablename_one
Does DTS SQL execute task support the like statement?
Secondly using the scenario above which does work in DTS, when I try to set the global variable dynamically that fails aswell:
SET table2 = 'tabletwo'
SET @CMDString = N'GlobalVariables("ImportTable").value'
EXEC @pReturn = sp_OASetProperty @oPackage, @CMDString, @table2
Anyone got any ideas please?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

The best thing you can do is use the 'Dynamic properties task'.

If you haven't used it before, let me explain.

Practically all of the objects in a dts package can be set from this task. There are a number of ways these can be set within the task. Your scenario would suggest two ways.

1. Using an ini file, you can change the name of the table i the file rather than the package.

2. Probably a better way for you. Use a query against a table to get the value back. You can just update the table and then execute the package in one sql statement.

Check out BOL for more details ot http://www.sqldts.com. It's very easy to set up.

------------------------------
The Users are always right - when I'm not wrong!

Mark Bosomworth Say Hey Kid Points: 669 More actions · Answer 2

Thanks Jonathan,

1 - I am trying to set the tablename value dynamically from a table which changes daily, so an ini file is only adding to the complexity.

2 - I am trying to use a query to get the value back. Firstly DTS does not seem to like the 'like ?' statement in the Execute SQl task. And secondly I need to set the input parameter which changes daily as above.

I hope I have clarified the situation a little. Ambiguity comes as standard i'm afraid.

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 3

Sadly, you can't use ? with like.

Put an ExecSQL task upstream that finds your table name based on the like criteria. Dump today's table name into an output variable. You can then later use it as an input variable in your criteria as where tablename = ?

[font="Courier New"]ZenDada[/font]

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 4

Mark,

Try something like this:

SELECT fieldname1

FROM tablename

WHERE fieldname2 Like ('%' + ? + '%')

Then set the input parameter to your global variable and if you want you can also set the fieldname1 to an output parameter mapped to a global parameter.

Good luck,

Darrell

Mark Bosomworth Say Hey Kid Points: 669 More actions · Answer 5

Thanks Darrell,

The change in syntax works fine now. And the global variable is working today aswell. These little things can be so frustrating.

Thanks for your help.

chuz Mark