?
Solved

DTS questions

Posted on 2004-03-23
8
Medium Priority
?
353 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
0
Comment
Question by:irinag
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 14

Accepted Solution

by:
adwiseman earned 450 total points
ID: 10658945
Using the parameters in SQL tasks is very limited as to what the Designer will allow you to implement.  There are a few workarounds.

First, only pass in the paramter once.  If you have some SQL that needs in in multiple places, do it like follows


Declare @v1 int, @v2 int

Set @v1 = ?
Set @v2 = ?

--- your SLQ, procedure calls using @v1, @v2


To get the designer to accept this code you must trick it.

Get the SQL task to accept your parameter by doing the following

SP_WHO ? --You will need sysntax that will alow you to enter all your parameters in a given order.

After you set your parameter for the above script, paste in your script, with the parameters Declared and set in the same order, and click ok.  It will now run.  I'm going to try and find my answer for this I gave a couple of months ago, it was step by step directions.

0
 
LVL 2

Author Comment

by:irinag
ID: 10659146
but i am calling Oracle Pl/SQL procedure (actually packaged procedure) , not MS SQL sproc
0
 
LVL 34

Expert Comment

by:arbert
ID: 10659237
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"....
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 2

Author Comment

by:irinag
ID: 10659568
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
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10659824
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.
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10659857
Here's another example of how to trick it into accepting your parameters.

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20909916.html
0
 
LVL 34

Expert Comment

by:arbert
ID: 10660256
"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).
0
 
LVL 2

Author Comment

by:irinag
ID: 10660514
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
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question