topic Re: DI Studio - Unresolved reference to table/correlation name in Delete task in SAS Data Management

DI Studio - Unresolved reference to table/correlation name in Delete task

Tom_Bailey — Wed, 22 Nov 2017 13:52:37 GMT

I'm running an extract from a SQL Server table. Before I load into my destination table, I want to remove any keys in the destination table that have a match in the work table (W8T0KH11) I produced from my extract task.

The destination table has a PK on 'TransactionKey'. If I don't run the deletion, the proc append that is run in the Table Loader errors due to the key already existing. The reason I want to delete and re-append is because the rows have been updated at source.

In the delete task, I have said where W8T0KH11.TransactionKey = DirectoryBaskets.TransactionKey. This fails when I try to run the delete stating "ERROR: Unresolved reference to table/correlation name W8T0KH11."

I'm of the opinion that for some reason the work table is not being created before I try to run the delete. From the log it is defined and then immediately moves to process delete.

I want to have these deletions run before the table loader and appreciate any pointers.

Flow screenshot :

Re: DI Studio - Unresolved reference to table/correlation name in Delete task

LinusH — Thu, 23 Nov 2017 07:00:25 GMT

Spontanously it sounds more of a syntax rather than an execution error, a full log might answer that.
But I think you are doing this a bit more complicated than needed. The Table Loader transform lets you either update/overwrite rows on existing keys, or just skip adding them (which to chose depends on the purpose of the target table).

Re: DI Studio - Unresolved reference to table/correlation name in Delete task

Tom_Bailey — Thu, 23 Nov 2017 10:08:48 GMT

Hi,

Thanks for the response.

If you mean the Update/Insert Load style, I found this was performing individual loops per row. I have 700k+ rows to analyse and it was taking far too long (and had run up some impressive size logs).

Re: DI Studio - Unresolved reference to table/correlation name in Delete task

Tom_Bailey — Thu, 23 Nov 2017 10:14:32 GMT

.... actually looks like that was due to the technique I was using. Modify by index looks OK.

Thanks