We help IT Professionals succeed at work.

DTS questions

irinag
irinag asked
on
Medium Priority
368 Views
Last Modified: 2013-11-30
Hi,
 
i have few question about DTS :
- how to pass  global variable to PL/SQL procedure (when i put "?" as one of arguments - it says -" sql statement does not contain any parameters"

- how to implement  "if/case -logic"
i want to analyze the value of global variable and depending on this value  execute one  or another step ....

- i am executing 10 transformation concurrently and sending success email if all of them succeed, now i need to send a failure email if at least one of these 10 transformations fails. how to do this ?

thanks in advance,
Irina
Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
but i am calling Oracle Pl/SQL procedure (actually packaged procedure) , not MS SQL sproc
Top Expert 2004

Commented:
The above still goes.  YOu have to trick the driver into thinking you have a parsible SQL Statement--then you can add the parms.

If you need to evaluate the global variable, you probably want to read the global variable in an ActiveX script and then call the PSQL proc from there.

As far as sending failure emails, just put an additional workflow constraint that fires "on failure"....

Author

Commented:
oh well , the problem is how to trick it ....


about failure-if i connect several transformations
"on failure" with email task
theis failure email getting sent only if all transformations failed
but i need to do it if at least one fails
Add a AxtiveX task, that executes on completion of all steps in question.  Then do and

IF globalvariables.parent.task("SQL...task1").status = ...failure OR exp OR exp THEN
    main.status_fail
ELSE
    main.status_success
END IF


And add your mail task on the failure of this activeX script task.  It will execute if your

I don't have the exact syntax, but you get the idea.
Here's another example of how to trick it into accepting your parameters.

https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20909916.html
Top Expert 2004

Commented:
"oh well , the problem is how to trick it ...."

All you have to do is put in a valid Oracle statement that will validate against the data source.  Once you choose the parms you want from the parameter dialog, you can go back and change the PSQL statement to whatever you want (as long as the number of parms  are the same).

Author

Commented:
thanks guys,

looks like "tricking"  the parser works ...

About "AxtiveX task" - i am SOOO new to this ...
so it i have let's say  2 SQL tasks A and B - (no global  variables are modified there...)

I connect both  A and B to Active X task on completion and then what code  should i put ?

thanks in advance
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.