RE: Executing a DTS package from a Web application.
From: Mike D. (D._at_discussions.microsoft.com)
Date: 02/22/05
- Next message: oj: "Re: Linked SQL Server to Access"
- Previous message: Lee Gillie: "Re: DTSHelper ActiveX DLL"
- In reply to: yhay: "Executing a DTS package from a Web application."
- Next in thread: yhay: "RE: Executing a DTS package from a Web application."
- Reply: yhay: "RE: Executing a DTS package from a Web application."
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 22 Feb 2005 13:55:03 -0800
Is your IIS set up to use Windows authenication or anonymous access?
"yhay" wrote:
> 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: oj: "Re: Linked SQL Server to Access"
- Previous message: Lee Gillie: "Re: DTSHelper ActiveX DLL"
- In reply to: yhay: "Executing a DTS package from a Web application."
- Next in thread: yhay: "RE: Executing a DTS package from a Web application."
- Reply: yhay: "RE: Executing a DTS package from a Web application."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
