SSIS - Transfer SQL Server Objects Task - Problems with SMO Connection Manager

Question

I am using SSIS within Visual Studio 2012. I am planning to use the Transfer SQL Server Objects Task in SSIS to, well, transfer (nearly) all objects (schema only) between servers. I am unfortunately stuck at the SMO Connection Manager configuration step. While in the Editor for the task, I click on Source Connection and select <New Connection ...>. I then enter the name of the server I wish to use as the source. When clicking on Test Connection, I get a Failed to Server error message. I am able to connect to the server from SSMS, and I can make a successful connection if I create a Connection Manager in SSIS. I simply cannot successfully connect when using the SMO Connection Manager within the Transfer SQL Server Object Task.

Answer 1

I have just came across the same issue one hour ago in VS2012. There is a bug related to Visual Studio, which gives you this error message regardless the network connection when you add SMOServer type of connection and press Test Connection button before pressing OK button.

Therefore, what you need to do is to add SMOServer Connection, select your source and press OK without testing the connection. Then you can test the connection after making a right click and edit on the connection manager. The test works when you do in that way.

Answer 2

It's probably already too late, but I've realized that if you don't click on Test Connection and leave the Server Name, you can click OK, and when you hit the dropdown on SourceDatabase, the list of DBs appear.

Answer 3

In my case the solution was to enable TCP/IP protocol for instance (I was trying to connect to) in Sql Server Configuration Manager and in Properties of protocol on tab IP Addresses for IPAll set TCP Port to 1433.