|
|
You're in the
sql server dts
group:Run DTS from ASP
sql server dts:
I have a DTS package that imports data into SQL 2000 from an ODBC source on another server. There are only 2 steps, empty the sql table and import new data. This package runs with no errors when it is run from EM on the SQL server. I need to be able to trigger this package from a ASP page on an intranet server, but I cannot get it run correctly. Both steps return failed message to the asp page. I suspect it could be a permissions problem. What permissions do I need to set where? Thanks, Rogerc
What's the error message you get?
Axel Dahmen ------------ "RogerC" <rojoch@NOSPAMtiscali.co.uk> schrieb im Newsbeitrag news:OEvczAmMFHA.3708@TK2MSFTNGP14.phx.gbl... [quoted text, click to view] > Hi, > I have a DTS package that imports data into SQL 2000 from an ODBC source on > another server. > There are only 2 steps, empty the sql table and import new data. > This package runs with no errors when it is run from EM on the SQL server. > > I need to be able to trigger this package from a ASP page on an intranet > server, but I cannot get it run correctly. > Both steps return failed message to the asp page. I suspect it could be a > permissions problem. > What permissions do I need to set where? > > Thanks, > Rogerc > >
Hi Axel,
Thanks for responding - sorry about delay getting back. The result of ruuning the asp page is: Step [DTSStep_DTSExecuteSQLTask_1] failed Task "Execute SQL Task: Delete Table Data" Step [DTSStep_DTSDataPumpTask_1] failed Task "Transform Data Task: Import Data" Package [RPackage2] failed So the package is loading ok, but cannot execute the steps. What I cannot work out is what permissions are required where and what runs under what account. The job involves 3 servers - IIS, sql 2000 and an access database source on another. When a workstaion calls the asp page on the IIS server does the package run on the IIS server? and with what account (IUSR_<WEBSERVER>) ? Im using sql authentication in the package to access the sql table. RogerC [quoted text, click to view] "Axel Dahmen" <NO_SPAM@NoOneKnows.invalid> wrote in message news:%23C%23nn$nMFHA.4076@TK2MSFTNGP10.phx.gbl... > What's the error message you get? > > Axel Dahmen > > ------------ > "RogerC" <rojoch@NOSPAMtiscali.co.uk> schrieb im Newsbeitrag > news:OEvczAmMFHA.3708@TK2MSFTNGP14.phx.gbl... >> Hi, >> I have a DTS package that imports data into SQL 2000 from an ODBC source > on >> another server. >> There are only 2 steps, empty the sql table and import new data. >> This package runs with no errors when it is run from EM on the SQL >> server. >> >> I need to be able to trigger this package from a ASP page on an intranet >> server, but I cannot get it run correctly. >> Both steps return failed message to the asp page. I suspect it could be >> a >> permissions problem. >> What permissions do I need to set where? >> >> Thanks, >> Rogerc >> >> > >
Hi Roger,
well, I thought you could get more precise on the errors appearing. But you are right, this may be a rights problem. Which authentication scheme are you using? IIS will execute the ASP page using IUSR_<webserver> unless you are running the application as a separate process. What code are you using to execute the DTS package? HTH, Axel Dahmen ------------ "RogerC" <rojoch@NOSPAMtiscali.co.uk> schrieb im Newsbeitrag news:OAzfG9jNFHA.4092@tk2msftngp13.phx.gbl... [quoted text, click to view] > Hi Axel, > Thanks for responding - sorry about delay getting back. > > The result of ruuning the asp page is: > > Step [DTSStep_DTSExecuteSQLTask_1] failed > Task "Execute SQL Task: Delete Table Data" > Step [DTSStep_DTSDataPumpTask_1] failed > Task "Transform Data Task: Import Data" > Package [RPackage2] failed > > So the package is loading ok, but cannot execute the steps. > What I cannot work out is what permissions are required where and what runs > under what account. > The job involves 3 servers - IIS, sql 2000 and an access database source on > another. > > When a workstaion calls the asp page on the IIS server does the package run > on the IIS server? and with what account (IUSR_<WEBSERVER>) ? > Im using sql authentication in the package to access the sql table. > > RogerC > > "Axel Dahmen" <NO_SPAM@NoOneKnows.invalid> wrote in message > news:%23C%23nn$nMFHA.4076@TK2MSFTNGP10.phx.gbl... > > What's the error message you get? > > > > Axel Dahmen > > > > ------------ > > "RogerC" <rojoch@NOSPAMtiscali.co.uk> schrieb im Newsbeitrag > > news:OEvczAmMFHA.3708@TK2MSFTNGP14.phx.gbl... > >> Hi, > >> I have a DTS package that imports data into SQL 2000 from an ODBC source > > on > >> another server. > >> There are only 2 steps, empty the sql table and import new data. > >> This package runs with no errors when it is run from EM on the SQL > >> server. > >> > >> I need to be able to trigger this package from a ASP page on an intranet > >> server, but I cannot get it run correctly. > >> Both steps return failed message to the asp page. I suspect it could be > >> a > >> permissions problem. > >> What permissions do I need to set where? > >> > >> Thanks, > >> Rogerc > >> > >> > > > > > >
Hi Axel,
The code I am using is directly from http://support.microsoft.com/?kbid=252987 with SQL authentication. This section from the paragraph titled 'Possible Permissions Issues' "make sure that the account executing the package has appropriate Windows permissions to all resources that the package may use--such as disk files, DLLs, or other drivers (for import/export operations)--especially when the Web site is configured for Anonymous access" is pretty vague! I have since had some success by doing the following: Installing IIS on the SQL server and getting the page to run from there. I had to give the Access database 'Everyone' read permissions to achieve this. I assume that this was because the IUSR__<webserver> account is a local account. Ensuring that when setting up the DTS package in Enterprise manager the SQL server name is entered correctly - if working directly on the SQL server it will default to 'local' and have to be entered manually as it only appears as 'local' in the drop down list. When this package is run on the IIS server it doesn't know what 'local' is. I now get the following on the SQL server, the IIS Server and from a workstation- Step [DTSStep_DTSExecuteSQLTask_1] succeeded Task "Execute SQL Task: Delete Table Data" Step [DTSStep_DTSDataPumpTask_1] succeeded Task "Transform Data Task: Import Data" Package [RPackage2] succeeded The section 'Using DTS Events' mentions creating a compiled ASP component to get access to the GetExecutionErrorInfo method. I will have to look into this as the errors returned from a script are not helpful Regards, RogerC [quoted text, click to view] "Axel Dahmen" <NO_SPAM@NoOneKnows.invalid> wrote in message news:OP3I3e6NFHA.3156@TK2MSFTNGP15.phx.gbl... > Hi Roger, > > well, I thought you could get more precise on the errors appearing. > > But you are right, this may be a rights problem. Which authentication > scheme > are you using? IIS will execute the ASP page using IUSR_<webserver> unless > you are running the application as a separate process. > > What code are you using to execute the DTS package? > > HTH, > Axel Dahmen > > ------------ > "RogerC" <rojoch@NOSPAMtiscali.co.uk> schrieb im Newsbeitrag > news:OAzfG9jNFHA.4092@tk2msftngp13.phx.gbl... >> Hi Axel, >> Thanks for responding - sorry about delay getting back. >> >> The result of ruuning the asp page is: >> >> Step [DTSStep_DTSExecuteSQLTask_1] failed >> Task "Execute SQL Task: Delete Table Data" >> Step [DTSStep_DTSDataPumpTask_1] failed >> Task "Transform Data Task: Import Data" >> Package [RPackage2] failed >> >> So the package is loading ok, but cannot execute the steps. >> What I cannot work out is what permissions are required where and what > runs >> under what account. >> The job involves 3 servers - IIS, sql 2000 and an access database source > on >> another. >> >> When a workstaion calls the asp page on the IIS server does the package > run >> on the IIS server? and with what account (IUSR_<WEBSERVER>) ? >> Im using sql authentication in the package to access the sql table. >> >> RogerC >> >> "Axel Dahmen" <NO_SPAM@NoOneKnows.invalid> wrote in message >> news:%23C%23nn$nMFHA.4076@TK2MSFTNGP10.phx.gbl... >> > What's the error message you get? >> > >> > Axel Dahmen >> > >> > ------------ >> > "RogerC" <rojoch@NOSPAMtiscali.co.uk> schrieb im Newsbeitrag >> > news:OEvczAmMFHA.3708@TK2MSFTNGP14.phx.gbl... >> >> Hi, >> >> I have a DTS package that imports data into SQL 2000 from an ODBC > source >> > on >> >> another server. >> >> There are only 2 steps, empty the sql table and import new data. >> >> This package runs with no errors when it is run from EM on the SQL >> >> server. >> >> >> >> I need to be able to trigger this package from a ASP page on an > intranet >> >> server, but I cannot get it run correctly. >> >> Both steps return failed message to the asp page. I suspect it could > be >> >> a >> >> permissions problem. >> >> What permissions do I need to set where? >> >> >> >> Thanks, >> >> Rogerc >> >> >> >> >> > >> > >> >> > >
[quoted text, click to view]
> Ensuring that when setting up the DTS package in Enterprise manager the SQL > server name is entered correctly - if working directly on the SQL server it > will default to 'local' and have to be entered manually as it only appears > as 'local' in the drop down list. When this package is run on the IIS server > it doesn't know what 'local' is. This might be your problem. You should have a look at "Connecting to Multiple Instances of SQL Server" in Online Books. Which server name are you using for oPkg.LoadFromSQLServer "MyServer" ? This should be the name to be used in the DTS connection string instead of <local> Did this help? Axel Dahmen www.sportbootcharter.com
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |