Solved

Sync pre/post Events - reuse existing db Connection info

  • 26 December 2023
  • 2 replies
  • 42 views

Userlevel 3
Badge +1

You can use post-job Events to run some arbitrary SQL (like a stored procedure) at the end of your Job.

The documentation says you have to make your own connection, as below.  But I’d really rather not embed cleartext credentials in multiple jobs.  Is there a way to specify a CData connection (which we’ve already established at this point), in the Event?

What I’d really like is the ability to run arbitrary SQL commands against the target table, after each Task completes, not just at the end of the Job.  Seems like this could be a parameter that we supply as we define each Task.

<api:set attr="db.driver"  value="System.Data.SqlClient"/> 
<api:set attr="db.conn" value="Data Source=sqlserver;Initial Catalog=Test;User ID=user;Password=pass;"/>
<api:set attr="db.query">
INSERT INTO MyLog (JobName, RunDate, Results) VALUES ('[_input.JobName]', '[null | now()]', '[_input.JobStatus]')
</api:set>
icon

Best answer by Ankit Singh 27 December 2023, 07:45

View original

2 replies

Userlevel 3
Badge +1

Nevermind -- I think “Transformations” feature is what I really should be using here.

Userlevel 5
Badge +1

Hey @DougN Nouria 

Glad you figured it out. You can use an ‘After Job’ Trigger to trigger your transformation once a job run completes: https://cdn.cdata.com/help/ASJ/sync/SQL-Transformations.html#trigger-category.

Reply