Running SSIS package on Always On and getting error
-
Hello,
so My ssis Package i created, does 2 things:
1.it first checks if the Table exists, and if not, create it
2.checks if Store procedure exists, if not creates it using a Dynamic tsql statement
so far, i made several ssis packages with it including several Execution SQL Task, and works fine, just this particular one doesnt work, and errors out and here is the error message below:
[Execute SQL Task] Error: Executing the query "
IF (NOT EXISTS (SELECT *
FROM ..." failed with the following error: "The target database, 'Test', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
so the issue is, I am trying to query a table that i need Read-Only Intent for SSIS, but not sure how or where to do that for Visual studio 2015?
-
Does your connection string include applicationIntent= read only?
Have you configured read only routing?
What account is calling the package?
-
no, how do i set that in SSIS? specifically for a Execution SQL Task?
-
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
thank you that fixed it 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply