Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
iowa12345
Starting Member
2 Posts |
 Posted - 2012-09-03 : 14:18:17
|
| Hi,kind of basic questions here but i think there could be some subtleties. (running sql server 2008 R2)Question 1:i am copying rows from a table on server A to a table on server B. i create the package in SSIS and it runs fine in BIDS.i can execute it from DTEXECUI and it runs fine.will not run from TSQL. getting "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON". so this must be a permissions thing here, right?related question 2:i would like the source and destination servers to be changeable at run time, from within SSMS using TSQL (or a SP). what's best way to do that?thanks for your help!Fred |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
iowa12345
Starting Member
2 Posts |
Posted - 2012-09-03 : 15:44:49
|
Hi and thanks for your response.1. yes, that sql server is set for access to xp_cmdshell. is there some other related permission that might be the problem here?2. thanks for this good link. i am using either package store or sql server to store the package, haven't tried File. i'll wait till i get the first thing resolved before going to this.quote:
Originally posted by visakh16
do account you use have access to run xp_cmdshell? for the 2nd question you can pass value for package connection properties while calling through dtexec in procedureseehttp://www.mssqltips.com/sqlservertip/2135/run-ssis-using-xpcmdshell-in-a-sql-server-stored-procedure/------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-03 : 19:31:42
|
quote:
Originally posted by iowa12345
Hi and thanks for your response.1. yes, that sql server is set for access to xp_cmdshell. is there some other related permission that might be the problem here?2. thanks for this good link. i am using either package store or sql server to store the package, haven't tried File. i'll wait till i get the first thing resolved before going to this.quote:
Originally posted by visakh16
do account you use have access to run xp_cmdshell? for the 2nd question you can pass value for package connection properties while calling through dtexec in procedureseehttp://www.mssqltips.com/sqlservertip/2135/run-ssis-using-xpcmdshell-in-a-sql-server-stored-procedure/------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
1. did u check other permissions also like involved server dbs as well as filepaths if any.2. there's no need for it to be in filesystem. you can have it even in sql server and in package store. just use /S instead of /F switch in dtexec callhttp://msdn.microsoft.com/en-us/library/ms162810(v=sql.105).aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
running pkg from SSMS