VARIABLE Dynamic List 1

[erezba]

Hello
I am trying to run sql script icluded in an Execute Sql script control.

The code should run on a certain list values.

The code is somthing like this this:

where
[tablename].[fieldname] in (value1,value2...)

My question is:
Is there a way to set the dynamic list in advance (I call it dynamic since, sometimes there is 1 value sometimes 2 values and so on. I don't know it in advance).

Can I configure a varaiable that recieves a dynamic list?

I guess that if it's possible than the direction is Input, but except this I have no other idea as to the way of implementing this.

Thanks in advance
Erez

%-)

 

[MDXer]

You could try creating a variable of type String, then use a script task to create your comma delimited list and populate your variable with this list.

If you can explain in more detail your process maybe someone can come up with a better solution.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[erezba]

Thanks
MDXer

I am not familiar with the script task property, maybe it is the best the solution.
I will try to be more specific, and simlify the example.

Suppose I have a table containing visits of people at the the medical clinic. In each visit a medical code is writeen down.
Now lets soppose I want to select from this table, all the people having hypertension. If I know in advance that hypertension is denoted by medical code a,b,c, my script than will use the syntax

select [Visits].[Id]
from [Visits]
where
[Visits].[Code_Visit] in (a,b,c)

Now suppose that another desease (let's say Diabetes for example) is denoted by code e,f.
the code than will be

select [Visits].[Id]
from [Visits]
where
[Visits].[Code_Visit] in (e,f)

and so on for other deseases. As you can see I don't know in advance the codes number nor their names. I sometimes run the package on Hypertension, another time on Diabetes, and so on, for as many deseases that exists, and as being reqiered at a specfic time-point. Of course the package in which this code ancapsulated in 'Execute Sql script' control, does more than this, but for the problem I face, I simlified and concentrated on this specific step, inside the package

I hope I have succeeded in clarifying it now.
I am not familiar with the script task property, since SSIS is rather new for me, So it might be the solution as you say.Do you think it is?

Thanks again
Erez

 

[MDXer]

Sorry for yet more questions. You say you execute this code through an SQL task. Since the SQL Task is in a control flow you can't do anything with the results. Is this package just firing a stored procedure that then goes and does some work and doesn't return a result set?

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[erezba]

Hi
again

The results from the code I sent you + other Sql Tasks, are eventually used for creating text file results,that contains a record for each person that the process found relevant for the the desease explored.
The package is built from serveral Sql Tasks. The one I asked about is one of them.
No stored procedure is used here, neither a result set. Onlt simple sql query which I simlified in my previous mail.
I only want to feed the parameters relevant to the desease explored and that I know in advance, into the "where clause" I sent earlier.
Instead of changing manually the code inside the Sql Task control,I thoght some kind of variable could be positioned there, and it's values will be given by me, before running the package. I assume this is the logic standing behind variables use.

Thanks
Erez

 

[MDXer]

Yes you can do it the way you are thinking but a bit different than the way you are trying.

Create a variable such as MySQLQuery with a Type String make the value

select [Visits].[Id]
from [Visits]
where
[Visits].[Code_Visit] in (%CODES%)

Create a Codes variable with type string
Create a variable such QueryToRun with a type of string

In the properties for the QueryToRun set the variable property EvaluateAsExpression to True make the expresion

REPLACE(@[User::MySQLQuery],%CODES%,@[User::Codes])

Use this variable as the source of your sql command.

The reason I was asking is that while your process will work there may be a more efficient way of doing this.


Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[erezba]

I understand the logic of your answer but I don't know how to technically implement it.

I have to create 3 user variables of a type string?

a. QueryToRun
b. Codes
c. QueryToRun

???


The first one's value property is the code,
The second's value is the codes separated by commas (e.g 'a','b','c'),
As to the third variable - QueryToRun - where is the EvaluateAsExpression?
no value property like the first two?



I have to mention again, that the code I sent earlier, was very much simlified in order to clarify the question. It is a little bit more comlex. I use the 'select into' code to create a temporary table, which I use in the steps following inside the package. The logic however still stands - I am using a list of codes, to trace the population in question.

Can I still put my code as the value property of MySQLQuery variable?

Thanks
Erez

 

[erezba]

Hi
Paul

I found out the EvaluateAsExpression property, and managed configuring it.

I put QueryToRun as a source variable in Execute sql Task control, after changing the property SqlSourceType property to variable.

Is this what you meant?

Thanks
Erez

 

[MDXer]

from your description I believe so yes. I still believe that if you step back and look at what can be accomplished in a data flow you would be better served doing it there.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[erezba]

MDXer
Thanks for your help.

Erez