Executing a DTS package from a Web application.
From: yhay (yvette.hayesNOSPAM_at_ngc.comNOSPAM)
Date: 02/16/05
- Next message: bsanders: "Exporting BLOBs to .jpg files"
- Previous message: G Stark: "Re: Capturing Execute SQL Task result information"
- Next in thread: Mike D.: "RE: Executing a DTS package from a Web application."
- Reply: Mike D.: "RE: Executing a DTS package from a Web application."
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 16 Feb 2005 14:35:11 -0800
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.AppSettings["dtsServerName"];
string dtsPackageName =
System.Configuration.ConfigurationSettings.AppSettings["dtsPackageName"];
string dtsUserName =
System.Configuration.ConfigurationSettings.AppSettings["dtsUserName"];
string dtsPassword =
System.Configuration.ConfigurationSettings.AppSettings["dtsPassword"];
dtsp.LoadFromStorageFile("\\\\llny1hayesyv2\\dts\\XLS_to_IEP_DOCS_test_yy.dts",null,null,null,dtsPackageName,ref pVarPersistStgOfHost);
dtsp.Execute();
dtsp.UnInitialize();
// force Release() on COM object
System.Runtime.InteropServices.Marshal.ReleaseComObject(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
- Next message: bsanders: "Exporting BLOBs to .jpg files"
- Previous message: G Stark: "Re: Capturing Execute SQL Task result information"
- Next in thread: Mike D.: "RE: Executing a DTS package from a Web application."
- Reply: Mike D.: "RE: Executing a DTS package from a Web application."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
