×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using parameters in DTS

Using parameters in DTS

Using parameters in DTS

(OP)
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.

RE: Using parameters in DTS

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

----signature below----
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

RE: Using parameters in DTS

(OP)
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.

RE: Using parameters in DTS

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

----signature below----
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

RE: Using parameters in DTS

(OP)
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.

RE: Using parameters in DTS

Right on smile

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

----signature below----
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

RE: Using parameters in DTS

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.

RE: Using parameters in DTS

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

CODE

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

Then, to capture this value:

CODE

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 smile

Hope this helps,

Alex  

----signature below----
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

RE: Using parameters in DTS

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.

RE: Using parameters in DTS

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

Post back if you have any problems (or not smile )

Good Luck,

Alex

----signature below----
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

RE: Using parameters in DTS

This might help you out as well:

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

----signature below----
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close