How to invoke a service from another service?

[ashutosh]

Hi Team,

We are trying to invoke a service from another service. Can you please let us know what are steps that we have to follow.

FileInput—–>Service1—–>Service2

In the above flow we receive an input file and service1 starts. (Service 1 processes the file and inserts the data in two tables in database.) We want to truncate the tables before we start inserting the data in two tables. We are thinking to have the truncate logic in a separate service.

We tried with the below code for truncating the tables using lambda activity inside the service1 itself. But this doesn’t work.

(begin-transaction (sql-execute (((invocation-context)'metadata)'getDataSource "StagingDatabase") "delete from NJSRBCReconRecord" ) (sql-execute (((invocation-context)'metadata)'getDataSource "StagingDatabase") "delete from NJSRBCReconReport" ) )

1 2 3 4 5 6 7 8 9 10

(begin-transaction (sql-execute (((invocation-context)'metadata)'getDataSource "StagingDatabase") "delete from NJSRBCReconRecord" ) (sql-execute (((invocation-context)'metadata)'getDataSource "StagingDatabase") "delete from NJSRBCReconReport" ) )

Appreciate any help on this.

Regards,
Ashutosh

0

[Ed Shaw]

Ok. Many questions in this one.

First of all… “How to invoke a service from another service?”

There are a couple of ways to do this.

1) The Invoke Service step from the service palette. Simply drag this step onto your diagram and fill out the Service Name, Message, Output Channel and Arguments. This will directly invoke the service in-line. Processing will continue in the parent service after the child service returns.

1b) Use the (invokeService service-name message output args… function in script. Effectively the same as using the Invoke Service step, but in code. Again, Processing will continue in the parent service after the child service returns.

2) The Send (asynchronous) or Send/Receive (synchronous) steps from the service palette
Drag either or these steps onto your diagram and fill out the Output argument with a channel name. This will send your current message state to that channel and if that channel is bound to another service, the service will be called. And again, processing will continue in the parent service after the child service returns.

3) Set the output channel of one service to the input channel of another. If the services will be run in serially i.e. the first one will finish then the next one will run with its results. You can simply set the output channel of the first service to be the input channel of the second service.

Next… How do you use (sql-exec… to truncate a table

1) using (sql-exec

From the documentation…

(sql-execute ds sql bindParams…): function

Executes the given sql (update/delete/insert queries only) against the given data source. Supports macros from Exec steps in Main.upgrade such as ${table:}, etc. (For internal use only. Will be deprecated in the near future.)

Arguments
ds string|DataSource The data source.
sql string The SQL statement to execute – DML or DDL.
bindParams any (Optional) Bind parameters to be bound to the query.
Returns
integer The number of rows affected by the query.

You will notice that this is “For internal use only. Will be deprecated in the near future.” so buyer beware.

That said, your snippet of code should work. The first parameter can also be a string so you could have called it as…

(begin-transaction (sql-execute "StagingDatabase" "delete from NJSRBCReconRecord" )

1 2 3 4 5

(begin-transaction (sql-execute "StagingDatabase" "delete from NJSRBCReconRecord" )

It could be that you aren’t getting to a commit so you could try a (pre-commit) after your statement or it could be that the table is not being specified completely enough. I used [lei].[dbo].[NJSRStageTemplate] for the table name in my test which worked.

2) Using an ETL Activity
I think it is better if you create an ETL Activity in the persistence layer and use a “Truncate Table” step. This way you are using the model instead of going direct to the database, which is always preferable. To invoke an ETL Activity from your service, simply use the (SysETLActivity’invoke name message output . args) function.

e.g. (SysETLActivity’invoke “LEILoad” this () (this’body))

For that matter, if you are using your service to load a table, an ETL Activity may be the better way to go. I plan on writing a blog entry on loading data from a file using ETL Activities in the near future.

I hope this helps,

Ed

1

[ashutosh]

Thank you Ed.

I got good knowledge on the service part.

In our service we have different steps. first step is to clean the two tables that we are going to use. So I was using the a lambda pallet with the code that I sent you with my question. And after that step I am loading one of the tables reading a .csv file. But when ever I start my service I was getting error in the lambda step(if i run commenting the code in lambda service works fine). The code that I am using in lambda works fine in the scratch pad. So I was assuming that same thing should work in lambda pallet inside the service.

You are suggesting to use ETL tool to truncate the tables. I will use ETL activity to truncate the tables 🙂

Regards,
Ashutosh

0

[Author]

Posts