Transformer SQLExecute failed

[spalsikar]

Hi,

We have recently started using the Ascential tool and pretty much new to this tool. I am getting the following error message while trying to load the data from one ODBC table to another ODBC table. Any help/suggestions will be greatly appreciated.

Job_name..Transformer.DataIn: DSD.BCIPut call to SQLExecute failed.

Thanks,
Shashi

[ogmios]

Did you specify an SQL statement (either generated or your own) in the database stage from which you retrieve your data?

Ogmios

[spalsikar]

There is a sql statement generated in the retrieving data stage.

[ogmios]

There is a sql statement generated in the retrieving data stage.

The error is on the the stage connected to "DataIn" link, is this your retrieving stage?

Ogmios

[spalsikar]

No, it is not.

~Shashi

[ogmios]

No, it is not.

~Shashi

OK, then you have a problem at the other side... Check the stage that is attached to link DataIn on the other side of the transformer. Something is wrong with its SQL statement.

Ogmios

[KeithM]

The problem is in your insert/update statement since in the error is happening during the Put. This could be caused by something as simple as trying to update a key that isn't there. Look at the log through Datastage director. It should give you more information including the exact record that is causing the error. If you still can not figure it out from this then copy the SQL statement from the log and run it through whatever SQL tool that you have. This will give you the exact error message that is being returned by your database.

I hope this helps.

[spalsikar]

Thanks a lot for all your help guys. After checking the log file I found the following message

[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-01400: cannot insert NULL into ("PWADM"."PS_LEDGER_SHA"."ERROR_FLAG")[/b]

It's kind of confusing, It looks like three new fields are being added to the Target datastage and they are LOAD_DTTM, LASTUPDDTTM,ERROR_FLAG. I don't see these fields on the Source side. Does Ascential tool always add these three new fields to all the Records (tables) on the target side?
How can I fix it? I am not understanding why it is trying to load a null value in the ERROR_FLAG filed. It is a very simple ETL map.

Thanks
Shashi

[chulett]

In Oracle, when you insert data into a table and do not mention all of the fields, the ones that are not mentioned get default values. Unless you override them in the DDL, the default value for all fields is a NULL. This doesn't work out so well when the fields do not allow nulls.

You'll need to add these new fields (and whatever transformations they need) to your job.

[KeithM]

The additional fields that your seeing were not added by Datastage, they were added by someone who can create and modify tables in your database. One way to tell is that Ascential is a little more more consistent with their naming standards.

I'm guessing that the physical table layout in your database does not match the layout in your target stage. You probably need to refresh this from the database to get those additioanl fields added. As Craig said, if you don't specify a value for these columns they will default to null.

[spalsikar]

We are using Ascential ETL maps to extract, transform and load data between different PEOPLESOFT databases. Most of the Target (I guess all of the Transcation records) records contain these three fields LOAD_DTTM, LASTUPDDTTM,ERROR_FLAG (I guess Ascential does that to keep track of any modifications/changes that have been performed since the last load) by default and these three fields are not part of the source record. I did check the delivered maps and they also have these three fields.

Finally, I got the map working by passing a value using a constraint for the ERROR_FLAG field. Thanks for all the sugestions, I should have mentioned PeopleSoft in the first place.