sql-serverssisetlsql-server-2016copy-database-wizard

MSSQL 2016 Package execution failed | SSIS error: Index was out of range


I've made a copy job with Copy Database Wizard using SMO(leave DB online). The DB is to be copied to the same SQL instance. This fails with

The package execution failed. The step failed.

If I execute the SSIS package it has created, the following occurs:

Event Name: OnError Message: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index StackTrace: at System.Collections.BitArray.Set(Int32 index, Boolean value) at Microsoft.SqlServer.Management.Smo.PermissionWorker.GetPermissionSetBase(PermissionEnumKind kind, Int32 i) at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPermissionsFromCache(PermissionEnumKind kind) at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AddScriptPermissions(StringCollection sc, PermissionEnumKind kind, ScriptingPreferences sp) at Microsoft.SqlServer.Management.Smo.Database.AddScriptPermission(StringCollection query, ScriptingPreferences sp) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateSpecialUrn(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptUrns(List1 orderedUrns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List1 urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(UrnCollection list, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.Smo.DataTransferBase.GetScriptLoadedTransferWriter() at Microsoft.SqlServer.Management.Smo.Transfer.TransferData() at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()

I have tried starting the job from SQLCMD as well. Same results. I have tried Detach/Attach method as well. That went through fine, however, that is not a desired option as it leaves the DB offline for the transfer's duration.

I have admin rights on the server(Windows) and the instance as well. Executing the package(.dtsx file using Package Execution Utility) outside SSMS gives the same error. I have created an empty database with only one table in it. Same error happens.

Please help..

OS: Windows Server 2012 MSSQL: 2016 SP1


Solution

  • This has been resolved with applying the January 2018 CU, even though this fix is NOT documented there.