"Timeout expired" when executing Copy Database Wizard package

Question

I have a fresh install of SQL Server 2012. I went though the Copy Database wizard in SSMS to copy a database from a different server, and saved it as a package. I tried running it as a job, but it failed with the following error:

The job failed. The Job was invoked by User user. The last step to run was step 1 (CDW_test_Step).

I tried executing the package with the Execute Package Utility just to see if it works that way, but got the following error:

An error occurrred while transferring data. Seee the inner exception for details.... Timeout expired. The timeout period elapsed proor to the completion of the operation or the server is not responding. CREATE DATABASE failed...

I made sure the package is executing with the same account of the owner.

I created a simple job with TSQL, not an SSIS package, and it executed fine.

Any help is appreciated.

Update:

I checked the event log and I'm seeing a couple of these:

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {FDC3723D-1588-4BA3-92D4-42C430735D7D} and APPID {83B33982-693D-4824-B42E-7196AE61BB05} to the user NT SERVICE\SQLSERVERAGENT SID (S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool.

Answer 1

This is a bit of a strange error relating to the fact that the SQL server agent service account doesn't have permissions on the Integration Services DCOM object.

2 ways you can fix this:

1:/ Run the SQL Agent service as a user that has admin rights on the local server, administrators always have DCOM permissions (probably not the best idea though just for this)

2:/

Click Start -> Run then type dcomcnfg to open the component services tool

Navigate through Component Services -> Computers -> My Computer -> DCOM Config

Locate the Microsoft SQL Server Integration Services 11.0, right click it and select properties

Click on the security tab and under "Launch and Activation Permissions" click edit

Add the NT SERVICE\SQLSERVERAGENT and grant it local launch and activation rights

Try your import again

Answer 2

After consulting with someone who knows these stuff, I learned the issue is probably because the servers are 2 different versions (2008, 2012).

Instead, I created the DB objects (with generate scripts) once, manually, then set up a job that runs an import (through the Import/Export Data wizard).

..although with this method I'll have to keep the objects synced manually, which is a pain (at least without a 3rd party software like RedGate).