May 28, 2015 at 11:55 am
System(s) Principal Server SQL Server 2014 Enterprise 64 bit
32Gb Ram 4 CPU(s) Secondary server same as first Using AlwaysOn
I have created a "SSIS package" to transfer Jobs and Logins
from Principal server (A) to Secondary server (B). I created
a SSIS package in Microsoft Visual Studio 2012 Shell and once
completed executed the package in Visual Studio the package works fine.
I then Started SSIS on my principal server (A) and imported
the package into MSDB without sensitive Data (No encryption).
I then executed the package by right clicking on the package
and execute, again the package worked fine.
Next I created a Job on the Principal Server (A) and then set the
Job Step to Execute a SSIS package.
When I try and execute the package I get the following error:
The SELECT permission was denied on the object 'sysjobs_view',
database 'msdb', schema 'dbo' End Error
DTExec: The package execution returned DTSER_FAILURE (1).
I have created a PROXY and followed all the requirements for
running a SSIS package and so for that attempt I get:
"The specified @proxy_name ('SSISProxy') does not exist.". Even
though this Proxy user is displayed in the Drop down list in the job
under "Run As"
I have set up SSIS packages in the past to run in jobs on SQL Server
2005 and 2008 it seems quite a bit has changed in 2014???
I have looked on the forum as well as Online and so far did not find any
answers to why this is proving to be so difficult...
Any help would be appreciated ...
May 28, 2015 at 12:04 pm
Also look at the data connections in your package. They should be using Windows authentication, not SQL.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
May 28, 2015 at 12:15 pm
I was pretty sure when I set up the source and destination connections I used windows authetication...I double checked and tha is what they are set to use...Thanks
May 29, 2015 at 7:57 am
The specified @proxy_name ('SSISProxy') does not exist
Because this SSIS job accesses both SQL servers ensure that the proxy account exists on both servers has the correct permissions on both servers.
May 29, 2015 at 2:34 pm
Wilbur...
I tried creating the proxy account on the destination server and lo and behold it all worked...Thanks very much
Tell Mr. Ed I said Hello Wilbur...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply