Hi All,



I have a task I need to execute when a given condition in
transformation1 is met. I don't know how to address the required task
in the current instance of the package as there will be other instances
being executed. Transformation1 logs custom exceptions using the a
generic custom task "Task2" during each row transformation, hence when
an exception is met during each row transformation values are stored in
variables in the package, task2 when executed reads and stores the
variables into 1 or more tables.



All transformations are in VBscript and may use ADO and/or DTS objects
as needed or required.



Any help would be great.



Armen


--
Posted via http://dbforums.com

In article <3350585.1063153907 (AT) dbforums (DOT) com>, Armen
<member38857 (AT) dbforums (DOT) com> writes



As I understand your requirements, please correct me if I'm wrong....

You can call another DTS package from within ActiveX SCript, and you can
manipulate workflow to executes task or not, providing branching and
skipping of tasks, but it sounds like you have a job of work that you
wish to call from within a transformation script, so workflow will not
work. Calling another package whilst possible would have a high
overhead, since we are talking about a row by row process.

If this job of work is just logging, and it is needed from within the
transformation script it seems like a lookup would be ideal. Despite the
name lookups are just a way of calling some SQL from within a
transformation script. It does not have to look anything up, it doesn't
even have to return anything. Since you say the logging relates to
multiple tables, it would probably simplest to wrap this in a stored
procedure. Your lookup SQL is then just the call to the procedure,
passing in any parameters (values) required. This should be a lot faster
than any alternatives.

Some online resources on lookups-

How to Use Lookups in DTS
(http://www.sqldts.com/default.aspx?277,1)

(http://www.databasejournal.com/featu...le.php/1459171)

(http://msdn.microsoft.com/library/de.../en-us/dnsqlpr
o2k/html/sql00l5.asp)

Don't forget Books Online as well.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Hi Darren,



So the limitation is that i cant execute a given task within the package
being executed? Bummer! This would be so useful.



Oh well back to the drawing desk.



Armen


--
Posted via http://dbforums.com

In article <3355346.1063233259 (AT) dbforums (DOT) com>, Armen
<member38857 (AT) dbforums (DOT) com> writes

You cannot execute a task from within a transformation. I think trying
to do this would be a bad idea anyway since you really want to focus on
performance, and calling out to anything will slow it down.

As I said if you just want to run some SQL use a lookup. For anything
else why not write it within the ActiveX Script transform, or even write
you own custom transform, although this must be done in C++.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org