Execute DTS Package From ASP.NET

I am trying to execute a DTS package in my asp.net code, but the dts package has both a owner and user password. I want to be able to use the user password to execute the package within the code, but whenever I try that I get the following error:
Access to package properties requires entry of package owner password.

Line 101: oPKG.LoadFromSQLServer("(local)", "UserName", "Password", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "DTSPassword", , , "CopySourceTest1")Line 102:Line 103: For Each oStep In oPKG.StepsLine 104: oStep.ExecuteInMainThread = TrueLine 105: Next

It works fine if I execute with the owner password, it just will not work with the user password. Any ideas will be appreciated
Thanks

To run DTS package through a stored proc you either use DTSRUN.exe or XP_CMDSHELL which is SQL Server Agent dependent. Try the links below for DTSRUN.exe sample code and XP_CMDSHELL configurations with permissions. What I am saying is to run DTS with SQL Server Agent dependent service like xp_CMDSHELL you must give the account used to install SQL Server Agent Admin permissions in Windows and SQL Server. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

http://www.sqlteam.com/item.asp?ItemID=19595

|||

We aren't executing this through a stored procedure. I am importing the DTS reference. Here is the code that makes it work:

Dim oPKGAs DTS.Package

Dim oStepAs DTS.Step

oPKG =New DTS.Package

Dim sMessageAsString

'Load Package

oPKG.LoadFromSQLServer("(local)", "UserName", "Password", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "PackagePassword", , , "CopySourceTest1")

ForEach oStepIn oPKG.Steps

oStep.ExecuteInMainThread =True

Next

'Execute

oPKG.Execute()

oPKG.UnInitialize()

oStep =Nothing

oPKG =Nothing

I'm trying to figure out how I can run this code or something similar without having to execute the cmdShell stored proc and the DTS run utility. My question is, is it possible to execute this code with the User password on the DTS Package instead of the Owner password?
Thanks

|||I could be wrong but I don't think you can because all automation in SQL Server is SQL Server Agent dependent. Hope this helps.|||I am pretty new to this, but I just don't understand why it would work using the owner password though. If i go to Enterprise Manager I can execute the DTS package with the user password.
Also, I have another question that is kind of off the topic, but related. If I do not impersonate my user account the package does not execute. It give me an access denied error to the database .ldf file. Why do I need to impersonate my user account to execute it. I understand when I run this I am using the ASP.NET user account, but any other time that user can write to the .ldf file, for example when logging in or anything, correct?|||The text below is from Microsoft about SQL Server Agent permissions and read the post below for more info about using DTS with stored procs in an Asp.net application. Hope this helps.
("Important: SQL Server Agent will need local Windows administrator privileges if one of the following is true:
SQL Server Agent connects to SQL Server using standard SQL Server Authentication (not recommended).
SQL Server Agent uses a multiserver administration master server (MSX) account that connects using standard SQL Server Authentication.
SQL Server Agent runs Microsoft ActiveX? script or CmdExec jobs owned by users who are not members of the sysadmin fixed server role. " )

http://forums.asp.net/906564/ShowPost.aspx
|||

We decided that the code I am using will work, we will just use the owner password. But now, Are the permissions and user accounts the same for the way I am executing this? Everything I read seems to be executing it with the stored procedure xp_cmdShell and the DTS Run utility which I am not using. I am confused on why I need to impersonate my user account (which has administrator rights on the local machine). Why can't I just use the IIS Asp.net User account to execute it? When I execute the package impersonating my user account it will write to the database .ldf file and the package will execute but when I do not impersonate my user account it does not write to the .ldf file and the package does not execute, I get the access denied error. I guess I get kind of confused on the user accounts and permissions needed.

Thanks

|||

justn_m87 wrote:

I guess I get kind of confused on the user accounts and permissions needed.

I think you are just confused about the permissions needed to run SQL Server Agent dependent services and you are not alone, Microsoft just got to accept the permissions for SQL Server Agent because their customers could not get replication in SQL Server to work following their configuration guidelines. I don't know what to tell you DTS in Asp.net with stored proc must give SQL Server Agent correct permissions. You cannot use the IIS account because so many services in SQL Server are SQL Server Agent dependent so running IIS and SQL Server Agent on the same account is just not good practice. Hope this helps.|||Hello
Could U plz help me out of this problem
The problem is while executing a Dts package from aspx page
It is giving the error "Error System.Runtime.InteropServices.COMException (0x8004040E): Invalid GUID specified"
the code is :-
package.LoadFromSQLServer("(local)", "myuser", "mypwd",DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "","DB8112F5-7279-486E-97AF-ACBC333A611A","D4573855-FDC6-46B7-B644-04058B49299F", "OutterSync", "")
I have given the servername,username, password,GUID, Version ID,packagename and there is no owner password and user password for thepackage.
It is working fine with dtsrun utility with the same guid and ver id, but it is giving error Invalid GUID from asp.net.
Thanks
vikky