SCHEMA MUST HAVE A KEY - IS THIS PRIMARY KEY REALLY REQUIRED?

[Mitz]

Hello Team,

Good day.

I hope all is well. I'm a newbie in Talend and would like to seek your help. I've initially created this logic to extract the data from Salesforce and load it to the existing table in SQL Server. However, I am getting this error. I've changed it whether Update or Insert and I "Turned off the Identity Insert" box, still same error is being shown.

Error 1:

tDBOutput_1 For insert if not exist, Schema must have a key 

java.lang.RuntimeException: For insert if not exist, Schema must have a key

Error2:

tDBOutput_1 For update, Schema must have a key

Does it really required to set the Primary Key on both my input file (csv format) and in the existing SQL DB? I would like to verify before I do some modifications specially in the existing database/table.

Please advise. Thank you in advance.

Regards,

Mitz

[Anonymous]

Hello @Michelle Panis​ ,

For the Update and Insert action,

Yes, it's required to setup the Key column which is the same field as that in DB table side in the tDBOutput's schema settings.

Also, it's suggested to setup the Update Key/ Deletion Key in tDBOutput's advanced settings page like

 

[Mitz]

Thank you @Aiming Chen​ This is noted. I've set the Key column/ However, I got a new error "tDBOutput_1 Invalid column name 'ExtractionDate'" This column is actually new, that will be added to the existing table in SQL.

Please refer to the error below.

com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'ExtractionDate'.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265) ~[mssql-jdbc-actual.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1662) ~[mssql-jdbc-actual.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:615) ~[mssql-jdbc-actual.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:537) ~[mssql-jdbc-actual.jar:?]

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7417) ~[mssql-jdbc-actual.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3488) ~[mssql-jdbc-actual.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:262) ~[mssql-jdbc-actual.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:237) ~[mssql-jdbc-actual.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:483) ~[mssql-jdbc-actual.jar:?]

I've checked the modules related to sql and they were all installed already.

Thanks much.

[Anonymous]

Hello @Michelle Panis​ ,

Please remove the quotes for the column name 'ExtractionDate' in tDBOutput schema settings, it should be ExtractionDate

[Mitz]

Hello @Aiming Chen​ ,

I have managed to fix the "invalid column name". Instead of extracting to a csv file (deactivated the tFileOutputDelimited), I have directly load it to the SQL server. However, though I am no longer getting any error message (I assumed) except for this message highlighted in yellow, "Null value will be used for context parameter connection_SAFConnection_timeout: For input string: ""

SAFConnection is a context variable I've used. Having said that, I cannot see the data in the database when I logged on to the SQL server. Did I missed something?

[Anonymous]

Hello @Michelle Panis​ ,

The above message is a warning, you can ignore it.

Could you please double check if there is data fetched from Salesforce? you can add tLogRow to output the data before the tDBOutput (SQL Server db)

[Mitz]

Hi @Aiming Chen​ ,

Yes, there is data fetched from the Salesforce and I added the tLogRow component to see the output.

[Mitz]

Hi @Aiming Chen​ ,

I'd like to inform you that I was able to fix the error in Talend. The data is now being extracted without any errors. I just need to get somebody from Infrastructure team to check the permission of the SQL account I am using. Thank you for your help.