I can't see how data in the Subaccounts table is linked to AccountCategories.

How us SUB1 linked to Description1, SUB2 to Description2, etc?

Database security is one of those things which in my experience is difficult to handle within SSDT.

It can be handled in environment-aware post-deployment scripts, or completely outside SSDT. Outside SSDT...

Is it going to be impossible to discuss without sample data? If so, let me know and I will create a spreadsheet. If you have a test data generator...

Option 2 is probably the way to go. Cross database dependencies are very difficult to deal with in SSDT. I always found I had to do a certain amount...

As it's a full table scan, there's not so much you can do.

You could read this & then experiment with the DefaultBuffer settings.

Definitely use Fast Load and destination table...

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...

Rather than continuing down the path you're on, I suggest getting SSIS running as a domain service account user. Then you can assign the required privs to the service account...

Are server A and server B in the same domain?

Ugly, but this works:

DECLARE @SchedStart VARCHAR(25) = '2020-06-07-12.30.00.00000';

SELECT @SchedStart
      ,CAST(STUFF(STUFF(STUFF(LEFT(@SchedStart, 23), 11, 1, ' '), 14, 1, ':'), 17, 1, ':') AS...

If there are multiple different values for 'result' in any particular grouping, which one of those would you like to return?

If you've got SQL Search at the ready, try searching SSISDB procs for the text 'EncryptByKey'. I think that will indicate most, if not all, of the encrypted columns.

Out of interest, what do you mean by 'does not have the master key open'? I've never had to deal with anything like that.

Here's another way

DROP TABLE IF EXISTS #Stuff;

CREATE TABLE #Stuff
(
    SomeText VARCHAR(1000) NOT NULL
);

INSERT #Stuff
(
    SomeText
)
VALUES
('ID=205&SID=1005&TID=3')
,('ID=25&SID=1005&TID=3')
,('ID=2&SID=1005&TID=3')
,('ID=2006&SID=1005&TID=3');

SELECT s.SomeText
     ...

Please read this for details of how NOLOCK can make things go wrong.

I was seriously considering setting up a consultancy yesterday and offering my services to HM Gov at a fixed fee of £20m to set up a process using punched...