Using parameters in DTS 1

[jj22171]

I have a DTS package with a query to pick up data between Date1 and Date2. At present, these two variables are defined as Global Variables as parameters. If these variables are stored intead on a table on same database which can be managed/changed by another program (say, Access), how can the query in DTS pick them up? Thanks for the help.

 

[AlexCuse]

There are many ways you can do this. To assign the variables as output parameters of an execute sql task would probably be the easiest.

Hope this helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[jj22171]

Thanks Alex,

My Execute SQL task has the query "Select PRD1, PRD2 from tblPeriods". The Tranform Data Task has the query "Select * from tblData WHERE Date1=? AND Date2=?" where Date1 and and Date2 should pickup values from PRD1 & PRD2, respectively. And this is the part I don't know how to.
Appreciate your help.

 

[AlexCuse]

Hm, SQLDTS.com is down at the moment. That is where I would normally turn for an example.

Have a look at this:

http://msdn2.microsoft.com/en-us/library/aa933470(SQL.80).aspx

What you need to do is have output parameters from an execute SQL Task used to fill Global Variables. Then, you can use use these global variables in your second query as the parameters.

You need to use global variables for this so that they will be accessible to both tasks.

Does this make sense?

Hope it helps, and post back with any quesionts,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[jj22171]

Got it! The output parameter in execute SQL Task allows you to create global variables which can be used in Tranform Data Task. Thank you very much, Alex.

 

[AlexCuse]

Right on

Glad you got it sorted, sometimes all you need is a poke in the right direction

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[jambu]

This sounds like something I need to do can anyone fill me in with a few more details please?

I have an SP with an output parameter thus

create procedure spTest @ErrorTest char(20)
as
--stuff
Set @ErrorTest = 'Error'

I then have this in an Execute SQL task
declare @ErrorParam char(20)
Exec spTest @ErrorParam

I am not clear on what to do next, when I click on the Parameters button in the Execute SQL task I just get errors -
"Deferred prepare could not be completed.
Statement(s) could not be prepared.
'Char' is not a recognised CURSOR option."

How do I get output parameters from my proc into the global variables?

I want to be able to be able to force failure or success either in the Execute SQL Task or using global variables in an activex task following it.

thanks in advance.

 

[AlexCuse]

You need to change your SP so that your parameter is declared as an OUTPUT param:

create procedure spTest @ErrorTest char(20) OUTPUT
as
--stuff
Set @ErrorTest = 'Error'

Then, to capture this value:

declare @ErrorParam char(20)
Exec spTest @ErrorParam OUTPUT

select @ErrorParam

From there, you should be able to get it into your global variable. The main lesson to learn is, output parameters from SP's do not work the same as rowsets

Hope this helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[jambu]

Thanks Alexcuse, I did have the output after my parameter in the real code but forgot to put it in the example, doh!

I will try the other code tomorrow, one quick question - How do I get the value into a global? Do I click on the parameter button?

Thanks.

 

[AlexCuse]

Yes you will need to use an output parameter from the SQL task.

Post back if you have any problems (or not )

Good Luck,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

This might help you out as well:

http://www.sqldts.com/234.aspx

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom