I am trying to move all database tables from one db to another as a nightly job.
The first (export) db is running sql server 2008 – SQL Server Authentication – I connect with SQL Server Native Client 10.0 to that one. I should mention I only have read access to this server.
The second (import) machine where I want to save the job is running 2014 – Windows Authentication – I connect with SQL Server Native Client 11.0 to this one.
Copy all tables. I have tried both saving the SSIS package as SQL Server and File System. It throws an error when I actually try to save it. The error says, "The Server threw an exception." Additional information: The Server threw an exception. Super helpful.
the SQL Server instance is new, so I thought it might be a rights issue. I checked the event log and have these two errors 1000 Application Error and 10016 Microsoft-Windows-DistributedCOM.
For 10016 I tried this run -> dcomcnfg -> Component Services -> DCOM config -> MSDTServer100 -> Right click Properties -> Security -> Launch and Activation Permissions. Also, I am already an admin on this box.
In SQL 2014 I don't have MSDTServer100 So I changed permissions for these DTS Package Host (several of them) and Microsoft SQL Server Integration Services 12. Is there a different config I am missing?
- I should add, I ran this wizard locally and the export worked just fine. I couldn't save it because I only have SQL express locally.
I should mention I only have read access to the first server. I ran a Profiler trace. I see two errors User Error Message "Changed database context to 'msdb'" and "Changed language setting to us_english" the event on the trace is Audit Logout. I don't do this often enough to know, but I wouldn't say I'm seeing anything out of the ordinary.
Best Answer
Since you are getting the error when saving and not executing the package, you are using SQL 2014 you are probably being bitten by the new security features introduced in 2012.
Your user's login most likely doesn't have access to the SSISDB catalog (even though he might have access to SQL Server).
You need to map the login to an SSISDB user and configure access to the SSISDB folders/projects in Integration Services.
Please have a look at this MSDN blog post SSIS Catalog Access Control Tips and SQL 2012 SSIS Catalog Permissions