SSIS - Database is read-only

RoyB09 306 Reputation points
2023-01-28T13:44:22.23+00:00

Hello

I've got an SSIS package that displays a database is read-only error (full error below) when running a Truncate table query from a SQL task . The data base is not read only as I can execute the same task successfully from another package.

[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE dbo.MyTableName" failed with the following error: "Failed to update database "mydBName"

because the database is read-only.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly,

parameters not set correctly, or connection not established correctly.

The properties are the same for each task ...
User's image

Am I missing a property setting or something else in the package.

Thanks in advance

Ron

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
0 comments
Accepted answer
  1. ZoeHui-MSFT 32,586 Reputation points
    2023-01-30T02:08:48.3333333+00:00

    Hi @RoyB09

    1. Right-click database you need to mark as read-write
    2. Select “Properties”
    3. In the “Database Properties” window select “Options” tab
    4. Scroll down and find “State” tab, go to “Database Read-Only” statement and select “False“

    User's image

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2023-01-28T13:58:02.08+00:00

    What it says, the database is set to be read-only. This is not an issue with you SSIS package, but a setting for the database.

    If this is an instance where you are responsible yourself, you can do

    ALTER DATABASE yourDB SET READ_WRITE

    If there is a DBA who is in charge, I recommend that you talk to that person on Monday before you do anything. The database may be set read-only for a reason. In the meanwhile - enjoy the weekend!