DTS Package Ownership Question 1

[CrystalUser1]

Hi,

How to change the owner of the DTS Package? what is the command to change the owner of the DTS Package? I created the DTS package by loggin on with my user id and password. It shows the owner as my userid. If somebody want to run the package, will it run?

thanks

 

[EdwinGene]

Yes

 

[EdwinGene]

You can change a DTS Package owner by executing the following stored procedure from Query Analyzer.

Execute msdb..sp_reassign_dtspackageowner
@name = 'packagename',
@id = 'packageid',
@newloginname = 'newloginname'


You can get the package id by executing the following query:

Select Distinct name, id, owner From msdb..sysdtspackages Where name = 'packagename'


There is a short article on the web about changing DTS Package owner name if you want to read it:

http://databasejournal.com/features/mssql/article.php/1461511

 

[CrystalUser1]

Thank you EdwinGene,

I created a DTS Package with my Userid As owner. The User has to run this package through web site. When the user try to run, its failing. do i need to change the ownership of the package? if so, what user id should be there?

Is it SA?

thanks

 

[jhall01]

that sounds like the web user doesn't have access to exec on the package. Check your permissions for the web user.

 

[CrystalUser1]

That web user is not acutally the user in the sql server or windows.

I created a table to validate the user id and password and then it shows the import button.

I put the same user id in the users table for validation for the purpose of running the packge. still its not running.

my users table and data

USERID PASSWORD
administrator admin

package created by me scrystalUser1


i logon the web screen using administrator/admin and then run the packge from the packge created by the user scrystalUser1.

any suggestions?

thanks

 

[jhall01]

I assume you have a DSN or DSN-Less connection setup.

I hate to beat a dead horse but does the user defined in DSN or DSN-Less connection have access to exec the packages?

what code are you using to execute the package from the web?

Jon

 

[CrystalUser1]

I am not using DSN setup.

the following is the code i am using to execute the package from.

i changed the owner ship to SA.

private bool ImportSQL()
{
Package2Class package = new Package2Class();
object pVarPersistStgOfHost = null;
try
{
package.LoadFromSQLServer("MYSERVER", // server name
"sa", // server username
"ABCXYZ999", // server password
DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,
null, // if the package has a password, otherwise null
null, // probably null
null, // probably null
"pkgImportAddressData", // name of the DTS package
ref pVarPersistStgOfHost);
package.Execute(); // execute the package
return true;
}
catch (Exception ex)
{
return false;
}
finally
{
package.UnInitialize(); // unwrap the package
System.Runtime.InteropServices.Marshal.ReleaseComObject(package); // tell interop to release the reference

}

}


Thanks

 

[CrystalUser1]

I could run the above package on my local machine from my code. but when i deploy this code on the server on my clients machine. its not working.

thanks