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

HWilliams-2457 116 Reputation points
2023-04-24T15:42:47.6533333+00:00

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.

Azure SQL Database
{count} votes

2 answers

Sort by: Most helpful
  1. Alberto Morillo 33,251 Reputation points MVP
    2023-04-24T21:11:34.9233333+00:00

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

    0 comments No comments

  2. Sedat SALMAN 13,265 Reputation points
    2023-04-26T06:27:05.8633333+00:00

    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;
    
    
    0 comments No comments