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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply