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

Can I replace them all with a single SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED?

Yes.

Should I replace them all with a single SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED?

No.

At least not if...

• This reply was modified 5 years, 5 months ago by Phil Parkin.

If you post screenshots of your DB connection manager settings and of the OLEDB source config, we should be able to work out what is going wrong.

Unfortunately, the error message...