SSIS Transfer SQL Server Objects error on extended properties of the table indexes

user_5635476 0
Viewed 224

I have a SSIS task to migrate tables from one DB to another.

Source DB version: SQL Server 12.0.4422.0 Destination DB version: SQL Server 14.0.3281.6

SSIS target version: SQL Server 2016

The configuration looks like this: (I want to transfer extended properties as well)

enter image description here

Everything works fine when IncludeExtendedProperties option is set to False. But when I set it to True I am getting the following error:

Object is invalid. Extended properties are not permitted on 'dbo.TABLE_NAME.INDEX_KEY_NAME', or the object does not exist.

UPDATE:

So, after thorough investigation, I found out that all extended properties are transferred just fine. The issue only arises when there exists an extended property on the index of any column. Still unable to figure out that part...

database-migration ssis sql-server
1 Answers

I have finally figured out the cause and it might be helpful for others, so posting this as an answer.

In MSSQL if you don't define constraints(i.e Primary Keys) with names, it uses a generated names of type PK_COLUMNNAME_RANDOMNUMBER.

Now in SSIS, if you try to transfer the objects with generated constraint names to the new destination, the query that does it also creates a new generated names in the destination. So after the transfer the constraint names between the source and destination DBs are different.

In addition, the query that tries to transfer the Extended Properties from source to destination, is trying to use the same names for both.

So, here are possible workarounds that I tested to make this work:

  • All your constraints are using the predefined names.

or

  • you simply don't have extended properties on the constraints at all.

I'm not sure if there exists any other solution or if it was accounted in any later versions of SSIS.

user_56354760
Related