Skip to main content

The data pipeline reads data from a SQLServer Table Origin (JDBC) and loads into Oracle Table Destination (JDBC). i am trying to truncate the table before loading data into oracle destination table (JDBC). Is there any way to truncate the table before load data into destination?

@raghuramxlsoftek You would want to try Dataflow Trigger ‘Start Event’ for this purpose. 

Start Event option runs before any stage starts processing in the pipeline(Attaching example screenshots).
 a. Configure 'Start Event'  in General settings of the pipeline.

 b. Use the Start Event - JDBC Query tab to run a truncate table query. You might want to use <truncate table if not exists <table-name>>.

 

More details refer https://docs.streamsets.com/portal/datacollector/4.4.x/help/datacollector/UserGuide/Event_Handling/EventFramework-Title.html#concept_amg_2qr_t1b

Hi Pradeep,

 

Thanks for your inputs.

I tried your suggestions, but not able to truncate the table.

My destination is JDBC Producer (Oracle)

Here attached the screen shot for your reference.

 

Regards,

Raghuram

@raghuramxlsoftek I see you are getting an Oracle SQL syntax error. Truncate query I have shared is just a reference to understand the Start Event functionality. You might want to check https://docs.oracle.com/cd/A58617_01/server.804/a58312/newcha16.htm#:~:text=ORA%2D03291%3A%20invalid%20truncate%20option,command%20and%20retry%20the%20operation. 

 

I made the following changes to overcome the error:

  1. Deleted use database
  2. Changed the syntax to truncate table database_name.table_name

Thanks for suggestions.

 

Regards,

Raghuram

Just a bit more of a Tip certain permission sets can be restrictive in oracle for letting you truncate tables, However if you make a stored procedure that truncates the table, You can call the stored procedure from the start event as per the other answers on here..

 

Just incase you had a problem with non DBAs trying to do this! 

 

 

Reply


Terms & Conditions