Search:  
NamespacesDiscussions.NET v1.1Feedback
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...
 
 




Ad
MBR BootFX
Best-of-breed application framework for .NET projects, developed by Matthew Baxter-Reynolds and MBR IT
 
 Copyright © Matthew Baxter-Reynolds 2001-2008. '.NET 247 Software Development Services' is a trading style of MBR IT Solutions Ltd.
Contact Us - Terms of Use - Privacy Policy - www.dotnet247.com