ETL and Data Integration tools - Modern ETL software for everyone

My repository was set up to save to SQL server. It was going ok for a while until I linked up the reader and writer in the GetEllis transformation. Now, when I try to make changes to the transformation, I get an error that says \"The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.\"
If I back up the repository and then restore it to the MS Access repository, I can make changes all day long with problems but if I move it back to the SQL server I get errors again and it will not save. What could be causing this problem? I attached the repository for your review.

Thanks,
Kelly

Hello.

We failed miserable to reproduce the problem however thanks to the internet we have some ideas which might help you.

Background of the problem.

To have this error there must be several conditions met:

- Having a table with a cluster index and a column of datatype image or text.

- The text or image data must be too large to be stored directly in the row.

- The execution plan chosen must let the optimizer thinks there is more than one row to update (eventhough there is only one row upadted).

I found 2 viable solutions for us :

1 - Remove the clustered index : We do not want this solution (impact on performances)

2 - Adding a primary key constraint on the column that was used in the where clause of the update (we knew that this column was unique and does not allow null values).

With the second solution the optimizer now know (execution plan) that there will be only one row updated. It seems that the unique index previously present was not enough.


Plan of actions.

1 Backup repository
2 Switch to default MS Access Repository
3 open Create Repository Objects [SQL Server] sql script
4 remove all references to clustered

ALTER TABLE [OBJECTS_TREE] WITH NOCHECK ADD
CONSTRAINT [PK_OBJECTS_TREE] PRIMARY KEY CLUSTERED
(
[OBJECT_ID]
) ON [PRIMARY]
GO

TO

ALTER TABLE [OBJECTS_TREE] WITH NOCHECK ADD
CONSTRAINT [PK_OBJECTS_TREE] PRIMARY KEY
(
[OBJECT_ID]
) ON [PRIMARY]
GO

5 save and close SQL script
6 run the package to recreate the repository
7 switch to recreated repository
8 Restore repository

Let us know the outcome
Peter

I didn't work just removing CLUSTERED from the script. After playing around with it I found out that the indexes were still getting created as CLUSTERED. I changed the script again and instead of just removing CLUSTERED, I changed them all to NONCLUSTERED.

That fixed my problem! I can use the SQL Repository again!
Thank you!!