How do I wake up paused Azure serverless databases from SSIS?

Question

It's incredibly annoying that paused serverless Azure SQL databases make SSIS packages fail when they're paused. How do I work around this? The error occurs on the connection, so it can't be trapped in a loop. Because I've implemented a loop that pauses between connection tries. This allows the packages to run, but the job still reports as "Failed". I don't want to force the package to report "Success" and I don't want to use some PowerShell hack. It actually needs to fixed in the SQL Server driver, some setting like "MultiSubnet=True" that retries until the database wakes up.

Answer 1

Here you will find other ways to implement a retry logic on your SSIS packages until the Azure SQL Serverless finally wakes up.

Answer 2

Create a Script Task in your SSIS package to wake up the paused Azure SQL Serverless Database before attempting to connect to it like as follows (please modify accordingly)

string connectionString = "your_connection_string";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    connection.Close();
}
Dts.TaskResult = (int)ScriptResults.Success;