SQL connection timeouts in Integration Host

I had a Database Query ready to go. The SQL was the same statement I already trusted. As soon as the query ran, there was a timeout. Integration Host showed the error clearly. The workflow stopped. The rows never came back.

Firstly, what does that actually mean?

It means the wait is not a workflow setting. There is no timeout box in the UI. Database Query, Database Receiver, the SQL sender. Same Connection panel on all of them. There is a Data Provider dropdown. There is a Connection String. There is the SQL underneath. There is not a separate timeout field sitting next to any of that.

The simple answer is that Sql Client already knows how long to wait. You tell it in the connection string. But that raises the next question: isn't a timeout just a timeout?

No. There are two of them, and they do different jobs.

Connect Timeout is how long to wait for the connection itself, in seconds. The instance is slow to accept you. The network path is having a moment. The server name is wrong and it sits there until it gives up. That clock starts before any SQL runs.

Command Timeout is how long a query or command may run once you are connected, also in seconds. A SELECT that scans more than it used to. A command waiting on a lock. A table that has grown since the last time anyone looked at it. Different clock.

Raise Connect Timeout when you cannot get in. Raise Command Timeout when you got in, then the query sat there. Read the error. Integration Host tells you which wait you hit.

To me it feels like an awkward leftover from treating the connection string as a secret you paste once and never read again. However, it is what it is...

But then the important question is how to deal with it?

Well, now that I do my integrations in Integration Host, things have become simpler (yeah, like everything else). Open the activity. Look at Connection. Stay on Sql Client, not Sql Client (Legacy). The encryption post covers that dropdown.

Then add onto the connection string you already have. Do not start again. Keep Server, Database, and the credentials. Stick the two keys on the end.

Server=SQL01;Database=Orders;User Id=iface;Password=****;Connect Timeout=60;Command Timeout=120;

I used 60 and 120 because they are easy to read, not because they are magic. Use what the site actually needs. The values are seconds. Connect Timeout of 60 is one minute to get in. Command Timeout of 120 is two minutes for the query or command to finish.

If you only change one of them, change the one that matches the wait you are actually seeing. A connection error after a short pause is Connect Timeout. A query that starts and then dies later is Command Timeout.

The same Connection UI is on Database Query, Database Receiver, and the SQL sender. Same string. Same two keys. You set it on the connection the activity is using.

Give the wait the site needs. If a query needs two minutes because the table is large, say two minutes. If the connection is just slow to accept you, say that on Connect Timeout and leave Command Timeout alone.

Anyway. As soon as the query ran, there was a timeout, and Integration Host showed it. There is no timeout box. Add Connect Timeout and Command Timeout to the connection string, in seconds, and give the wait you actually need.