Stored Procedure executed from DTS

28 Jul 2006 6:41 PM

Fergus

How can I access the Package Object which is executing my Stored Procedure.
I see lots of examples of running DTS from stored procedure. What I want is
to find out which package is executing my stored procedure (via exec sql) so
I can log the package name and step name along with my results.

--
Thanks and Good Luck.
Fergus

28 Jul 2006 7:28 PM

David Portas

"Fergus" <msdnferg@newsgroups.nospam> wrote in message
news:394F0DA8-24C0-4654-88C7-A5DA4279E5F5@microsoft.com...
> How can I access the Package Object which is executing my Stored
> Procedure.
> I see lots of examples of running DTS from stored procedure. What I want
> is
> to find out which package is executing my stored procedure (via exec sql)
> so
> I can log the package name and step name along with my results.
> --
> Thanks and Good Luck.
> Fergus
>

Why not just pass the package name as a parameter to your proc? That's the
easiest way. You can't get at properties of the package object from a proc
unless the proc instantiates the package in the first place.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

28 Jul 2006 10:19 PM

Fergus

Because the proc doesn't have to be executed from a package.
Because it could be executed from different packages, so I wouldn't want the
people using it to hard-code a value.
I guess I could have them pass in GlobalVariables.parent as an object, but
that means operational people have to be programmers (and we have to change
the interface of every stored proc).
Seems to me you should always be able to discover the attributes of your
environment.

--
Thanks and Good Luck.
Fergus

Show quote

"David Portas" wrote:

> "Fergus" <msdnferg@newsgroups.nospam> wrote in message
> news:394F0DA8-24C0-4654-88C7-A5DA4279E5F5@microsoft.com...
> > How can I access the Package Object which is executing my Stored
> > Procedure.
> > I see lots of examples of running DTS from stored procedure. What I want
> > is
> > to find out which package is executing my stored procedure (via exec sql)
> > so
> > I can log the package name and step name along with my results.
> > --
> > Thanks and Good Luck.
> > Fergus
> >
>
> Why not just pass the package name as a parameter to your proc? That's the
> easiest way. You can't get at properties of the package object from a proc
> unless the proc instantiates the package in the first place.
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>
>

31 Jul 2006 6:18 AM

Wei Lu [MSFT]

Hi Fergus,

Thank you for your posting!

Based on my experience, I think the GlobalVariables is the only way you
could use to indentify which DTS package is execute your stored procedure.
Please try to refer the following article that you could find some
information in it.

http://groups.google.com/group/microsoft.public.sqlserver.dts/browse_frm/thr
ead/7b9fa4879a6cbfc5/7891163a37558332

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.