Can a SQL Server 2000 DTS Package be run from a Web application? If so, what
permissions, SQL Server roles, etc. are required?

I’ve been only able to find examples of Windows and VB applications. I
would like an example of a Web application (written in C#).

Background:
I have a SQL Server 2000 DTS Package that copies data from a MS Excel file
to a SQL Server database table. The SQL Server database is not local. It is
on a separate database server. The Excel file is local now, for testing and
development. However, the DTS package specifies the full UNC file name.

If I run the DTS package directly inside of SQL Server Enterprise Manager it
works fine. The Excel data is copied to the data base table.

I have saved the DTS package on my machine as a file.

I have created a Windows application (using VS .NET and C# code), added the
DTS COM Reference, and called the LoadFromStorageFile or LoadFromSQLServer,
Execute, and UnInitialize methods of the Package2Class.

The Windows app runs fine when I load the DTS package from the SQL Server
(LoadFromSQLServer) and when I load it from the stored file
(LoadFromStorageFile).

I have created a Web application which does basically the same thing as the
Windows application, however, the DTS package will only run from the stored
DTS file (LoadFromStorageFile), however, it fails on the “copy” step. (The
step that copies the data from the Excel file to the database.) I get the
following message:

“Step 'Copy Data from xxx_sample_data$ to [MYDB].[dbo].[MYTABLE] Step' failed”

In my web.config file I use the <IDENTITY impersonate = true …> tag as
follows:

<identity impersonate="true" userName="MYDOMAINNAME\ IMPERSONATEDUSERID "
password="mypassword"/>

The user ="MYDOMAINNAME\IMPERSONATEDUSERID " has a login and can access the
database in SQL Server.

The DTS package’s copy step failed. Looks like the web app cannot open the
Excel file OR it cannot write to the database table. I’ve tried giving
Read/Write/Execute/Modify privileges to the user to read the Excel file.
But, it still does not run. I suspect there is a privilege problem.

What other privileges are needed in SQL Server as well as the web server?
(My local machine is the web server.)

NOTE: if I use the IDENTITY tag and put in my Windows login ID and
password, it works!

Here’s an excerpt of my code:



Package2Class dtsp = new Package2Class();
dtsp.FailOnError = true;

string dtsServerName =
System.Configuration.ConfigurationSettings.AppSett ings["dtsServerName"];
string dtsPackageName =
System.Configuration.ConfigurationSettings.AppSett ings["dtsPackageName"];
string dtsUserName =
System.Configuration.ConfigurationSettings.AppSett ings["dtsUserName"];
string dtsPassword =
System.Configuration.ConfigurationSettings.AppSett ings["dtsPassword"];
dtsp.LoadFromStorageFile("\\\\llny1hayesyv2\\dts\\ XLS_to_IEP_DOCS_test_yy.dts",null,null,null,dtsPac kageName,ref pVarPersistStgOfHost);


dtsp.Execute();
dtsp.UnInitialize();
// force Release() on COM object
System.Runtime.InteropServices.Marshal.ReleaseComO bject(dtsp);
dtsp = null;


The execution of the following DTS Package succeeded:

Package Name: MY_DTS_PACKAGE_NAME
Package Description: DTS package description
Package ID: {282B76DD-FE43-45C5-843B-305A7112B800}
Package Version: {D7CABC95-F98D-4397-901C-71568079C2D8}
Package Execution Lineage: {B0B30902-BF36-48D6-A7E7-E24A70279D49}
Executed On: MYMACHINENAME
Executed By: IMPERSONATEDUSERID
Execution Started: 2/16/2005 3:01:21 PM
Execution Completed: 2/16/2005 3:01:21 PM
Total Execution Time: 0.04 seconds

Package Steps execution information:


Step 'Copy Data from xxx_sample_data$ to [MYDB].[dbo].[MYTABLE] Step' failed

Step Error Source: Microsoft JET Database Engine
Step Error Description:Unspecified error
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5000000

Step Execution Started: 2/16/2005 3:01:21 PM
Step Execution Completed: 2/16/2005 3:01:21 PM
Total Step Execution Time: 0.01 seconds
Progress count in Step: 0


--
yhay

Is your IIS set up to use Windows authenication or anonymous access?



"yhay" wrote:

In the "Authentication Methods" window of IIS properties, I have Anonymous
access checked and Integrated Windows authentication is checked in the
"authenticated access" section.

"Mike D." wrote:

When using 'anonymous access' you need to grant permission (or create a role)
for IWAM_<machine_name>, IUSR_<machine_name>.

In addition, you need to grant requisite permissions on the excel file(or
the directory where the excel file is located) for the IWAM_<machine_name>.

Ideally, the excel file should be on the same server as IIS.

"yhay" wrote:

Thanks Mike for your response.
The problem is that since this is a Web application, it is not known ahead
of time what the machine_names of the users are. I thought the way around
this was to impersonate a specific user by specifying the <identity
impersonate="true" userName="domainname\username" password="password" /> tag
in the web.config file. Do I need to set up IIS for Windows authentication?


"Mike D." wrote: