Adding multiple new columns with SQLite transformation fails

[JGRFCU]

I am trying to add two new string columns (Column A and Column B) to a table using the Transform tab.  Both column transformations use SQLite function to parse the data.  The task fails with a generic SQLite error:  "SQLite general error. Code <25>, Message <column index out of range>."

However, if I only add one new column the task runs correctly.  It is only when I try to add more than one new column, and both are using SQLite functions, that I get this error message.  I don't believe it is a data issue because I individually tested adding only Column A successfully and then only Column B successfully. I also tried adding Column A with the SQLite transform and Column B as just a literal string value (no SQLite) and this worked. I even tried using different SQLite functions for each column and it still fails when adding both. Always the same general error.

Is there any insight to what might be happening here?

Thank you!

SQL Server source. Snowflake target.

[Michael_Litz]

Hi @JGRFCU 

Please post the SQLite functions you used. I would like to try and reproduce this.

Thanks,
Michael

[JGRFCU]

Hi Michael,

The original functions are this:  strftime('%Y-%m', $ColumnA); and strftime('%Y-%m', $ColumnB);

I am parsing the year and month (YYYY-mm) from two date columns into new string columns.

Thanks!

[Michael_Litz]

Hi @JGRFCU 

I just tested in my task and this worked.

Is there anything else that may be different?

Thanks,

Michael

[DesmondWOO]

Hi @JGRFCU ,

I've tested on SQL server also, and it works successfully for both Full Load and CDC.

Could you give us following information?
1. Source database and Target database
2. DDL of the source table (please rename table and column names if data security is a concern)
3. Sample data 

Thanks,
Desmond

[Heinvandenheuvel · Accepted solution]

@DesmondWOO , @Michael_Litz  You guys cannot read!

>>> The original functions are this:  strftime('%Y-%m', $ColumnA); and strftime('%Y-%m', $ColumnB);

See that semicolon? I bet you didn't test with that, did you now?

@JGRFCU  This is surely a very silly - slap against the forehead - error. What made you decide to put that semicolon on the end? It is not supposed to be there and unfortunately is not flagged during test or with a single transformation, but with two it blows up.

Actually in my test (oracle source) the task crashed and did NOT report the error indicated but nevertheless I'm very sure that the semicolons at the end of the transformations cause the issue.

Hein.

[JGRFCU]

Wow thank you, very much a facepalm moment lol. It certainly was the semicolons that were the issue.  When I was looking up SQLite syntax, as I am very much a novice, all the examples ended with a semicolon and I didn't see anything in the Replicate documentation about not using them.  Didn't think they would be problematic, obviously I was wrong.  I've removed them and everything is running smoothly.  

Thank you again @Heinvandenheuvel !

Also thank you @DesmondWOO  and @Michael_Litz for your time and effort.

[Heinvandenheuvel]

Fwiw, I do think this is worth a support ticket, internally or by a customer. It is both a runtime bug and design time weakness. It was readily spotted this time but could be more tricky under other circumstances such as in my test case silently crashing the task process 

[DesmondWOO]

Hi @JGRFCU ,

I can reproduce the same problem on the SQL server that you mentioned in the case. As @Heinvandenheuvel mentioned before, please create a support ticket. We will follow up with the R&D team.

Regards,
Desmond