ExamGuru IT/.Net Guru : trigger/sp to run dts on ASPFriends.com 'ngfx-sqlclient' list

.NETGURU

trigger/sp to run dts

Messages

Related Types

This message was discovered on ASPFriends.com 'ngfx-sqlclient' list.

Jeff Palmer

-- Moved from [aspsqlhowto] to [ngfx-sqlclient] by Tim <Click here to reveal e-mail address> --

Can I use a stored procedure to run a dts package? If yes, what context
will the dts package be called under if the stored procedure is called from
the web. My dts package uses a connection which accesses a local odbc
connection. It will bomb if the context is not correct.

I initially wanted to call the dts package from the web but could not get
the permissions configured correcty.

thanks,

Jeff P

Reply to this message...

David L. Penton

Check here:

http://www.swynk.com/friends/green/ExecDTS.asp

for options.

hth...

David L. Penton, Microsoft MVP
JCPenney Application Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach"
Click here to reveal e-mail address

Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
SQL BOL: http://www.davidpenton.com/sqlbol

-----Original Message-----
From: Jeff Palmer [mailto:Click here to reveal e-mail address]

-- Moved from [aspsqlhowto] to [ngfx-sqlclient] by Tim
<Click here to reveal e-mail address> --

Can I use a stored procedure to run a dts package? If yes, what context
will the dts package be called under if the stored procedure is called from
the web. My dts package uses a connection which accesses a local odbc
connection. It will bomb if the context is not correct.

I initially wanted to call the dts package from the web but could not get
the permissions configured correcty.

thanks,

Jeff P

Reply to this message...

Bill Swartz

Jeff, yes you can use a sp to call a dts package..(not sure what context,
but I assume it would be the user that is executing the proc).

You absolutely can execute the DTS package from the web also.

Here is a site that might help you with some of this stuff. He's got some
good examples both from the web, and from a procedure.

http://www.sqldts.com

Bill

-----Original Message-----
From: Jeff Palmer [mailto:Click here to reveal e-mail address]
Sent: Friday, August 16, 2002 10:46 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] trigger/sp to run dts

-- Moved from [aspsqlhowto] to [ngfx-sqlclient] by Tim
<Click here to reveal e-mail address> --

Can I use a stored procedure to run a dts package? If yes, what context
will the dts package be called under if the stored procedure is called from
the web. My dts package uses a connection which accesses a local odbc
connection. It will bomb if the context is not correct.

I initially wanted to call the dts package from the web but could not get
the permissions configured correcty.

thanks,

Jeff P
| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Reply to this message...

Jeff Palmer

I've checked out that site and others previously and none provide good info
for security settings. This DTS package uses a local DSN for ODBC access.
It is at this stage the the package bombs. It is my guess that at this
point, some permissions issue prevents this odbc connection. I have tried
several differnet permissions adjustments but is not my strong suit when
configuring across SQL, the Web, and Windows2k. Any ideas?

-----Original Message-----
From: Bill Swartz [mailto:Click here to reveal e-mail address]
Sent: Saturday, August 17, 2002 2:44 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: trigger/sp to run dts

Jeff, yes you can use a sp to call a dts package..(not sure what context,
but I assume it would be the user that is executing the proc).

You absolutely can execute the DTS package from the web also.

Here is a site that might help you with some of this stuff. He's got some
good examples both from the web, and from a procedure.

http://www.sqldts.com

Bill

-----Original Message-----
From: Jeff Palmer [mailto:Click here to reveal e-mail address]
Sent: Friday, August 16, 2002 10:46 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] trigger/sp to run dts

-- Moved from [aspsqlhowto] to [ngfx-sqlclient] by Tim
<Click here to reveal e-mail address> --

Can I use a stored procedure to run a dts package? If yes, what context
will the dts package be called under if the stored procedure is called from
the web. My dts package uses a connection which accesses a local odbc
connection. It will bomb if the context is not correct.

I initially wanted to call the dts package from the web but could not get
the permissions configured correcty.

thanks,

Jeff P
| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Reply to this message...

Bill Swartz

Jeff,

I know that DTS can be very frustrating.. Keep in mind a couple of things.

1. DTS runs locally. If you are accessing a file, it needs to be on the box
that the DTS package runs on unless you specify a servername/share. (am
sure about being local, but have not tested calling the file remotely)

2. The DSN does not hold the username and password. You have to specify
this when you open up your connection.

3. DTS always runs in the context of the calling user. So, this user needs
access to the DTS package, AND all the associated DB objects that the DTS
package acts upon. IE: inside your DTS package you must obviously have a
connection object. The calling user must also be able to make that same
connection.

4. DTS packages by default are not secured and have no passwords (anyone
should be able to run them) If you have assigned either an owner, or an
operator password, you will need to use these when you call dtsrun.

If none of that works, send me the error message and we'll go from there.

Bill

-----Original Message-----
From: Jeff Palmer [mailto:Click here to reveal e-mail address]
Sent: Monday, August 19, 2002 6:52 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: trigger/sp to run dts

I've checked out that site and others previously and none provide good info
for security settings. This DTS package uses a local DSN for ODBC access.
It is at this stage the the package bombs. It is my guess that at this
point, some permissions issue prevents this odbc connection. I have tried
several differnet permissions adjustments but is not my strong suit when
configuring across SQL, the Web, and Windows2k. Any ideas?

-----Original Message-----
From: Bill Swartz [mailto:Click here to reveal e-mail address]
Sent: Saturday, August 17, 2002 2:44 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: trigger/sp to run dts

Jeff, yes you can use a sp to call a dts package..(not sure what context,
but I assume it would be the user that is executing the proc).

You absolutely can execute the DTS package from the web also.

Here is a site that might help you with some of this stuff. He's got some
good examples both from the web, and from a procedure.

http://www.sqldts.com

Bill

-----Original Message-----
From: Jeff Palmer [mailto:Click here to reveal e-mail address]
Sent: Friday, August 16, 2002 10:46 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] trigger/sp to run dts

-- Moved from [aspsqlhowto] to [ngfx-sqlclient] by Tim
<Click here to reveal e-mail address> --

Can I use a stored procedure to run a dts package? If yes, what context
will the dts package be called under if the stored procedure is called from
the web. My dts package uses a connection which accesses a local odbc
connection. It will bomb if the context is not correct.

I initially wanted to call the dts package from the web but could not get
the permissions configured correcty.

thanks,

Jeff P
| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Reply to this message...

Jeff Palmer

Thanks, Bill. I took the easy way out, though, and used a dummy table with
triggers to call the DTS packages I need to run. I spent a great deal of
time working with security within the dts package connection object, and
with all involved tables, and still came to no resolution. Thanks for your
time. I will probably be spending more time dealing with permissions
settings before I attempt this same process again.

-----Original Message-----
From: Bill Swartz [mailto:Click here to reveal e-mail address]
Sent: Monday, August 19, 2002 12:46 PM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: trigger/sp to run dts

Jeff,

I know that DTS can be very frustrating.. Keep in mind a couple of things.

1. DTS runs locally. If you are accessing a file, it needs to be on the box
that the DTS package runs on unless you specify a servername/share. (am
sure about being local, but have not tested calling the file remotely)

2. The DSN does not hold the username and password. You have to specify
this when you open up your connection.

3. DTS always runs in the context of the calling user. So, this user needs
access to the DTS package, AND all the associated DB objects that the DTS
package acts upon. IE: inside your DTS package you must obviously have a
connection object. The calling user must also be able to make that same
connection.

4. DTS packages by default are not secured and have no passwords (anyone
should be able to run them) If you have assigned either an owner, or an
operator password, you will need to use these when you call dtsrun.

If none of that works, send me the error message and we'll go from there.

Bill

-----Original Message-----
From: Jeff Palmer [mailto:Click here to reveal e-mail address]
Sent: Monday, August 19, 2002 6:52 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: trigger/sp to run dts

I've checked out that site and others previously and none provide good info
for security settings. This DTS package uses a local DSN for ODBC access.
It is at this stage the the package bombs. It is my guess that at this
point, some permissions issue prevents this odbc connection. I have tried
several differnet permissions adjustments but is not my strong suit when
configuring across SQL, the Web, and Windows2k. Any ideas?

-----Original Message-----
From: Bill Swartz [mailto:Click here to reveal e-mail address]
Sent: Saturday, August 17, 2002 2:44 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: trigger/sp to run dts

Jeff, yes you can use a sp to call a dts package..(not sure what context,
but I assume it would be the user that is executing the proc).

You absolutely can execute the DTS package from the web also.

Here is a site that might help you with some of this stuff. He's got some
good examples both from the web, and from a procedure.

http://www.sqldts.com

Bill

-----Original Message-----
From: Jeff Palmer [mailto:Click here to reveal e-mail address]
Sent: Friday, August 16, 2002 10:46 AM
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] trigger/sp to run dts

-- Moved from [aspsqlhowto] to [ngfx-sqlclient] by Tim
<Click here to reveal e-mail address> --

Can I use a stored procedure to run a dts package? If yes, what context
will the dts package be called under if the stored procedure is called from
the web. My dts package uses a connection which accesses a local odbc
connection. It will bomb if the context is not correct.

I initially wanted to call the dts package from the web but could not get
the permissions configured correcty.

thanks,

Jeff P
| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Reply to this message...

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification