1

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.

Improve this question
2
  • You get the same error when trying to save the package to File System as you do saving it to the SQL server? I could see you not being able to save to SQL because that actually places it in MSDB, and if you have read-only permissions it wouldn't allow that.
    – Fredric Shope
    Commented Feb 2, 2016 at 20:59
  • Since you are targeting execution in a SQL Server 2014 installation, you can install SSDT-BI edition for SQL Server 2014 and then generate your packages proper. In fact, you can automate the creation of the packages quite nicely.
    – billinkc
    Commented Feb 3, 2016 at 1:17

1 Answer 1

Reset to default
1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.