Object Is Altered Outside Component Integration Services

Once an object is defined in Component Integration Services, alterations made to an object at the remote server are not made to the local proxy object definition. If an object is altered outside of Component Integration Services, the steps to correct the problem differ, depending on whether create existing table or create table was used to define the object.

To determine which method was used to define the object, run the statement:

sp_help object_name

If the object was defined via the create existing table command, the following message is returned in the result set:

Object existed prior to CIS.

If this message is not displayed, the object was defined via the create table command.

If create existing table was used to create the table in Component Integration Services:

  1. Use the drop table command in Component Integration Services.

  2. Create the table again in Component Integration Services using create existing table. This creates the table using the new version of the table on the remote server.

If the table was created in Component Integration Services using create table, you will drop the remote object when you use drop table. To prevent this, follow these steps:

  1. Rename the table on the remote server so the table is not deleted when you use drop table.

  2. Create a table on the remote server using the original name.

  3. Use drop table in Component Integration Services to drop the table in Component Integration Services and on the remote server.

  4. Rename the saved table in step 1 with its original name on the remote server.

  5. Create the table again in Component Integration Services using create existing table.

WARNING! Do not use drop table in Component Integration Services prior to renaming the table on the remote server, or you will delete the table on the remote server.

A good rule to follow is to create the object on the remote server, and then do a create existing table to create the object in Component Integration Services. This enables you to correct mismatch problems with fewer steps and with no chance of deleting objects on the remote server.