Component Integration Services is unaware of indexes that are added or dropped outside Component Integration Services. Verify that the indexes used by Component Integration Services are the same as the indexes used on the remote server. Use sp_help to see the indexes used by Component Integration Services. Use the appropriate command on your remote server to verify the indexes used by the remote server. For example, you can use the describe command with an Oracle server or select * from syscolumns, sysindexes for a DB2 server.
If the indexes are not the same, 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 object:
Use drop table in Component Integration Services.
Re-create the table in Component Integration Services using create existing table. This will update the indexes to match the indexes on the remote table.
If create table was used to create the object:
Use drop table to drop the index from the remote table.
Re-create the index in Component Integration Services using create index. This creates the index in Component Integration Services and the remote server.
An alternative method if create table was used to define the object is to turn on trace flag 11208. This trace flag prevents the create index statement from transmitting to the remote server. To use trace flag 11208, follow these steps:
Turn on trace flag 11208:
dbcc traceon(11208)
Create the index using create index.
Turn off trace flag 11208:
dbcc traceoff(11208)
