CODEWITHSUNDEEP
sql-serverssis
arcee123
arcee123

Reputation: 211

why does ssis transfer object stage return smo failure

I am trying to copy a database using SSIS Transfer SQL Server Objects Task.

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "An exception occurred in SMO.".
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Value cannot be null.
Parameter name: server".

I hit test connection and prove that I have the right server name and password. enter image description here

Upvotes: 3

Views: 2572

Answers (2)

Matei
Matei

Reputation: 13

I found out that if I am running the package locally from Visual Studio (VS), then I get the same error. My workaround is:

  1. Setup the SMO connection server as <my-server-name> in VS
  2. Deploy it to the server from VS
  3. Test-run it from the server itself (find the package in Integration Services Catalog > SSISDB, right-click and select 'Execute...')

<my-server-name> is the name of your server, could also be 'localhost'

For some reason VS does not get the server name, although I inserted it in the SMO connection. But running the package on the server itself, seems to have worked.

Upvotes: 1

Rekonn
Rekonn

Reputation: 31

My guess is bug was introduced with 2017 version of SSDT. To fix you need to down level the project from 2017 to 2016 using these steps:

  1. right click on the name of your project in the "Solution Explorer".

  2. go to the "properties" menu

  3. in the opened window titled "Deployment Property Pages", within the left-hand menu, drill down the "Configuration Properties" to the "General" attribute

  4. you'll see a tab titled "Deployment Target Version" on the right pane on the menu, there - choose "SQL Server 2016"

  5. click "OK" and agree to all later refactoring request and you're done.

Upvotes: 3

Related Questions