December 1, 2003 at 6:08 pm
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
December 1, 2003 at 9:08 pm
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
December 3, 2003 at 4:20 pm
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
-Francisco
December 4, 2003 at 7:58 am
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
December 4, 2003 at 8:18 am
How do you map global variable to a Parameter like
Update table set x='a' where y = ?
How do you map to ?
Thanks
December 4, 2003 at 9:40 am
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
-Francisco
December 4, 2003 at 11:30 am
This worked as advertised... thanks so much for your help :D. next is looking at how to get back the DTS OUTPUT Variables 😀
-Francisco
-Francisco
December 4, 2003 at 1:03 pm
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
December 4, 2003 at 2:59 pm
noeld, I don't get any results back from BOL w/ a search for sp_OA am I seraching under the wrong keywords?
-Francisco
-Francisco
December 4, 2003 at 5:56 pm
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
December 8, 2003 at 11:27 am
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
-Francisco
December 8, 2003 at 12:18 pm
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
December 8, 2003 at 2:04 pm
WOW, thanks for the reply, I'm going through the article right now 🙂 thanks again
-Francisco
-Francisco
December 8, 2003 at 4:56 pm
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
-Francisco
December 9, 2003 at 11:51 am
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
-Francisco
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply