RE: Executing a DTS package from a Web application.
From: yhay (yvette.hayesNOSPAM_at_ngc.comNOSPAM)
Date: 02/22/05
- Next message: Jamie Thomson: "Re: looping, importing"
- Previous message: Andre: "Re: looping, importing"
- In reply to: Mike D.: "RE: Executing a DTS package from a Web application."
- 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: Tue, 22 Feb 2005 15:01:02 -0800
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:
> 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: Jamie Thomson: "Re: looping, importing"
- Previous message: Andre: "Re: looping, importing"
- In reply to: Mike D.: "RE: Executing a DTS package from a Web application."
- 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
|
