sql server DB connector-Actions (execute custom query) connector output

[dareenhamdy]

I used a sql server DB connector and I configured it to execute custom query to loop on a list of items and insert them in DB 
the connectorOutputPayload variable which is the output of this step always returns [] even if the insertion in successful.

how can I detect the output of the DB insertion step in my Integration for error handling ? 

also another issue is that custom query task does not fail when I do an update statement with an id that does not exist

[AlexET]

Hi, thank you for your question and for your patience! We'll keep an eye on this conversation to ensure you get a response. In the meantime, we’d like to invite other community members to share their insights. 😊

[anjalisoni3655]

Hello team, We are actively looking into the issue, will keep posted.

[KyleMari]

Hi @dareenhamdy,

While we're looking into some questionable areas surrounding this issue, will you be able to share a sample test SQL query including the INSERT and UPDATE statements that can be used to reproduce the issue through the custom query task?

Usually, what connectorOutputPayload holds would depend on the result of the connector action. If the action is a SELECT query, then the result of that would be the value of that variable that's usually expressed in JSON format. It could be possible that the INSERT statement may work as expected with an empty [] result even if the outcome of that transaction itself is successful.

One way to validate if the insert transaction worked as intended is if you could conduct @@ROWCOUNT right after conducting an INSERT statement like this.

I hope my current feedback on the connectorOutputPayload helps.

[dareenhamdy]

When I configure the Connector task , with Action (execute custom query ) while using INSERT and UPDATE  sql statements  Both return [ ] in connectorOutputPayload variable even when they execute successfully.
sample statement.

INSERT INTO integration.dbo.[order] (OrderNumber, UserEmail, ItemName, Quantity, Price, VendorURL)

VALUES ('ORD67890','user@example.com', ?, ?, ?, '');

also the same issue happens if I configure Connector task , with Entities instead of actions and I choose (update, insert) they Both return [ ] in connectorOutputPayload variable, while the (select)

 @@ROWCOUNT can be a temp workaround I will give it a try but it will be nice to have any sort of indication if the execution in DB is successful or not in connectorOutputPayload object to detect how to complete the integration workflow and for better error handling.

[dareenhamdy]

when I tried this option with with Action (execute custom query ) on sql server connector there is a setback which is the custom query supports one operation at a time so I had to use 2 DB connectors one for the insert statement and another one to select the number of affected rows.

what If I want to execute a group of operations ,  If I want to read a group of sql statements from a file storage and then execute them on the DB.

which component/ task can I use ? or how can that be done ?

[nikhilsyadav]

Hi @dareenhamdy we have raised the query with the backend team and they are actively working on this use case.