DTS parameters, Input/Output

Question

Post reply

DTS parameters, Input/Output

fhtapia

SSC Eights!

Points: 994
More actions
December 1, 2003 at 6:08 pm

#107386

I want to pass 1 parameter to be processed by the dts package, how do you pass this parameter to a TSQL query in a DTS package? I'm looking in BOL but i'm up against the clock (of course :() and i may keep missing the important key that lets this happen
Thanks 😀
-Francisco

-Francisco

Viewing 15 posts - 1 through 14 (of 14 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

Make the Qry so that it maps a global variableto a parammeter like

Update tasble set x= 'a' where y = ?

then map ? to a global variable

AFTER you have done that you could run the dts package passing in the command line

/A global_variable_name:typeid=value

HTH

* Noel

fhtapia SSC Eights! Points: 994 More actions · Answer 2

First of all thanks for your reply it helped point me in the right direction... i'm still having trouble here is the error that I keep getting back

DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1

DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147220421 (8004043B)

Error string: The task reported failure on execution.

Error source: Microsoft Data Transformation Services (DTS) Package

Help file: sqldts80.hlp

Help context: 1100

my TSQL is simply MyDB.dbo.Sproc_StufftoDo ?, ?, ?

and I've named each parameter and given it data... I call the DTS as such:

EXEC master..xp_cmdshell 'DTSRun /S"ServerName" /E /N "TEST" /A MyParam1:String="jpowell" /A MyParam2:String="Test2" /A Param3:String="pool"'

is this right or am I veering off the wrong track again? 🙁

-Francisco

icata Hall of Fame Points: 3176 More actions · Answer 3

You can use the dtsrunui.exe application usually under C:\Program Files\Microsoft SQL Server\80\Tools\Binn to generate the correct syntax. 1. Advance 2. Generate.

This is what I got for a transfer from pubs to Northwind, base on the lname.

DTSRun /S "(local)" /N "Test Package DTSRUNUI.exe" /V "{9CD62314-53BC-4420-B1BC-9C9EC82F7B2D}" /A "lname":"8"="White" /W "0" /E

Ionel

younsoha SSC Enthusiast Points: 198 More actions · Answer 4

How do you map global variable to a Parameter like

Update table set x='a' where y = ?

How do you map to ?

Thanks

fhtapia SSC Eights! Points: 994 More actions · Answer 5

thanks for that I will try it...

younsoha

to learn how to map the variable to a ? read this intresting article I found yesterday....

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

-Francisco

fhtapia SSC Eights! Points: 994 More actions · Answer 6

This worked as advertised... thanks so much for your help :D. next is looking at how to get back the DTS OUTPUT Variables 😀

-Francisco

noeld SSC Guru Points: 96590 More actions · Answer 7

if you want Outputs you can:

1.

- run package with the sp_OA procedures

- store result on global variable

- read global variable

2.

- Store output on table in sql server

- Query table to get result

3

forget about DTS and use osql to run your queries

there can be more but this is just an example

* Noel

fhtapia SSC Eights! Points: 994 More actions · Answer 8

noeld, I don't get any results back from BOL w/ a search for sp_OA am I seraching under the wrong keywords?

-Francisco

noeld SSC Guru Points: 96590 More actions · Answer 9

go to the "index" and type sp_OA the list of them should come up

http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_14_7bcc.asp?frame=true

* Noel

fhtapia SSC Eights! Points: 994 More actions · Answer 10

Here is what I'm trying to do:

Execute the DTS package from a sproc, and still be able to get the values from the Output Parameters from the DTS... I couldn't figure out how I could use the sp_OA variables, maybe I'm overlooking it's usefulness 🙁

Anyhow, I followed the steps outlined in that webpage I posted before...

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

So I got working input parameters sent from a sproc, but I don't have any output parameters working... any ideas?

Thanks I appreciate this tremendously

-Francisco

noeld SSC Guru Points: 96590 More actions · Answer 11

This Example Illustrates pretty much ALL you need. The only thing missing maybe the sp_OAGetProperty method but once you read the article its VERY easy to get that functionality working

HTH

* Noel

fhtapia SSC Eights! Points: 994 More actions · Answer 12

WOW, thanks for the reply, I'm going through the article right now 🙂 thanks again

-Francisco

fhtapia SSC Eights! Points: 994 More actions · Answer 13

Ok, reading through the sp_OA BOL files I found a critical point that is going to be hindering in my operation... 🙁

Only members of the sysadmin fixed server role can execute sp_OASetProperty.

The only reason this is of any significance is because of what I'm trying to do...

The following may be a little long.. but please bear with me....

I want to create a Temporary ID and Password that belongs to the proper roles by user attempting to login.

so an application would connect with a generic login and would only have rights to run one sproc passing the actual UserID and Password that is in the sql table. On a match, it would create a new temporary userid on the fly along with a temporary password and adding them to the appropriate role.

I've created the set of sprocs that can do this, but do to the inability to have nested sproc carry the creator's rights, I began looking at exceuting the whole thing from a DTS package... now passing the parameters has been successful but getting the output to come back has been a bit of a challenge. so it would seem that sp_OA is also going to be out of the question.. and thats too bad because it looks awesome and more straight forward to implement.

Thoughts Ideas, Comments and even flying tomatoes are welcomed... ...

-Francisco

fhtapia SSC Eights! Points: 994 More actions · Answer 14

noeld, I finally came to the conclusion you had wich was to forget about DTS all together and just use osql.

This has fixed my syntax and results problem just fine 🙂 Thanks for all your help 🙂

-Francisco