can't publish transformed table in TIC

when try to publish table from cleanse and transform to TIC, it gave this error

“Object reference not set to an instance of an object”

tried the solution below, didn’t work.…/69101

Usually there is a full error stack when you get an error like this. Can you post the full error stack? This will help us see where it originated from. Also, can you attach your transformation and cleanse file? We won’t be able to run it but we can see if a rule is causing the issue. One more thing, does this error occur if you try to export to excel? Does it seem to be caused by one specific rule?

This is the error mesg i am getting. the error doesn't occur when I export to excel. besides replace and trim, I got this error when I use any of other functions in transform such as group, convert, to_char, to_number etc

Please help..

First we should confirm you can successfully publish using that connection.

  1. Go to the object explorer and click on a small table.

  2. right click and choose publish.

  3. Publish as View. Is this successful?

If successful here is the next step.

  1. In the Transformation and Cleanse window you should have a SQL tab.

  2. Click the SQL button at the bottom of the window and send to a SQL Editor. (Or copy and paste from SQL tab)

  3. Does this SQL execute successfully?

yes, I can publish tables to TIC.

After I sent it to Transformation and Cleanse, I tried to convert one column from datetime to string. the SQL tab became greyed out.

After I sent it to Transformation and Cleanse, I tried to convert one column from datetime to string. the SQL tab became greyed out.

Okay. I can reproduce the same issue in TDP 3.7. This has been fixed already in TDP 3.8 which is due out next month. Instead of using the convert datatye function use the format rule. This allows you to format a number or date and the end result is a formatted string datatype. This publishes without error.


I found this happens if I create a new calculated field. the sql will become grey out. if I apply functions to the existing column, it works. lot of times, we’d like to keep the existing column as a reference and create a replicated column to work on it

But let me point out a few things.

  1. The Calculated column rule as two modes. One uses SQL to execute the calculation and the other uses our internal engine. The mode that uses SQL is the checkbox on the right that says Use SQL Transform

  2. If all of your rules can be translated into SQL then you can publish the output as a View or Snapshot. This can be desirable if you want to refresh your data with these rules. If SQL cannot be used then the published output will always be a dataset only.

  3. To keep the original column when creating a new calculated column you only need to supply a name for the new calculated column. We support both replacement of the original column value or the retention of that column and creating a new column.

Let’s end this thread as the error you originally stated seems resolved. For a new question or issue please start a new thread:)