Insert and update DB table fails when input umlaut character

[chulett]

And the characterset of both the source and target are... ?

[arunpramanik]

And the characterset of both the source and target are... ? ...

Our Source/target MS SQL DB (Collate) is Latini1-General
and we do not have NLS for datastage.

But I have doubt whether character set has to do anything with my problem, as because this job runs fine when I execute again after failure without any change and even with the same data having these umlaut/German characters. The failure happens during the nightly refresh

[ray.wurlod]

Is the night run under a different user ID from the one used for the successful run?

[arunpramanik]

Is the night run under a different user ID from the one used for the successful run? ...

No its same user id for OS
And DB user is a parameter .. and we use same user ids for manual runs

[asorrell]

You say you don't think its related to NLS, yet the only time you have a failure is when there's an umlaut present? I'm not certain I follow your reasoning. Even if it is intermittent, it ONLY happens when there's an umlaut character present.

It sounds like there's a problem because you are encountering NLS characters in a non-NLS environment, and the error handling is inconsistent.

I'd try setting up an NLS-enabled project (if you can) and testing your job with the character maps set correctly for both source and target.

As an alternative - I'd write a little pre-processing program at the O/S level that replaces umlauts with "u's". Its better than getting aborts or "?" in the target data.

[arunpramanik]

You say you don't think its related to NLS, yet the only time you have a failure is when there's an umlaut present? I'm not certain I follow your reasoning. Even if it is intermittent, it ONLY happens when there's an umlaut character present.

Yes, because if run the same job/rerun whole refresh again after the failure it works fine with same characters.

Its better than getting aborts or "?" in the target data.

Strange because after sucessfull rerun with the same data we find exactly the data that should be in the dimension; No "?" characters is inserted in the target.

Not getting any clue

[arunpramanik]

Now the job simply fails most often even without umlaut character in the input, run perfectly when I execute after failure with the same data.

The sequential file in the job is overwritten every night.
Error message is the same

Any idea how to resolve?

[chulett]

You mean, other than what has already been posted?

[arunpramanik]

You mean, other than what has already been posted?

My problem is same - the jobs fails. Earlier I thought it was due to umlaut characters, now it is certain umlaut character is nothing to do with the failure

[ray.wurlod]

Change your Array Size to 1, at least while testing, so that the OLE provider can accurately report the actual row in which the allegedly invalid data occurs.

[arunpramanik]

Finally ...

Our problem

We have job design which inserts and update rows in tables reading data from sequential file as below

INSSEQ file ----> XFM ------> MS OLEDB (insert rows in MS SQL DB Table)

UPDSEQ file ----> XFM ------> MS OLEDB (Update rows in MS SQL DB Table)

We get the following following three Warning lines

OLE DB provider error HRESULT=0x80004005. - Warning
Array <N> has invalid row(s). - Warning
OLE DB provider error HRESULT=0x80004005. - Warning

Reason:
We tried to replicate the problem (lucky that we were able to replicate that) and put the trace on at the DB. And found SQL Error 1205

So we have a situation of Deadlock where we trying Insert and update the rows where insert and update process both are doing table scan.

Solution:
1. Create an Index on the table columns based on which we updating the table.

or

2. If Index creation is not allowed- change job design so that Insert/update operation done in one after another.

If any one have any other idea

[chulett]

Finally... we get the full picture of both the error and the job design that is causing it. Posting the complete job design in your initial statement of the problem could have gotten us here quite a bit sooner.

The index may help, but since your picture shows two separate streams embedded in one job, my first suggestion would be along the lines of #2 - split that into two jobs and run them one after the other, not simultaneously.

ps. Inserts don't do "table scans". Updates do but not inserts.

[ray.wurlod]

True (what Craig said) but a sufficiently severe setting of transaction isolation level can cause even inserts to take a page-level or table-level lock.

[chulett]

Fair enough.

[arunpramanik]

Thanks Craig

As per suggestion I will change the job design (not going for separate jobs)

INSSEQ file ----> XFM ------> MS OLEDB ------- dummy----->UPDSEQ file ----> XFM ------> MS OLEDB