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.