October 13, 2020 at 5:13 pm
Hi,
I have a large script that is querying and updating two databases on the same SQL Server instance (one client database and the MSDB). I'd like to publish this script in an SSIS package, but when I do so it complains that I don't have authorization to update the MSDB database. I have created a connection for the client database, and for some reason was under the impression that if I just used the [Database].[Schema].[Table].etc format to update MSDB, it would work as long as the referenced database is on the same instance (which of course it is). Am I mistaken?
Thanks in advance for any advice.
October 13, 2020 at 5:38 pm
Are you sure that the user executing the script has rights to update MSDB?
As this is a large script, it's usually considered good practice to put it into a stored procedure and to call the proc from SSIS.
It's not always possible, but if you can it's a good idea to avoid direct cross-database references when developing in SSIS. For example, create a resultset by querying database A and then pass that resultset to database B for onward processing. This has the benefit of requiring separate connections to each database and means that the databases do not directly depend on each other. Moving database B to a separate instance in future becomes a much easier task if the databases are not directly dependent.
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply