Thanks for your help. That gives me the error:

Message

Unable to start execution of step 1 (reason: line(10): Syntax error). The step failed.

This is the code:

$sqlConn = New-Object...

Hello,

Thanks for your help.

Apologies I didn't mean to be vague. I've now added the commandtext to my original post. At the moment the table has one row and the filename...

Hello,

Thanks for your help. I hadn't seen that and it it looks interesting. I will take a look.

I didn't specify when I created my post but I was more thinking...

Thanks for your reply.

All: Thank you for your help. I put in some code to update the stats and it seems to be solve the issue consistently and now I...

I've discovered that the table is updated overnight and the issue re-occurs overnight.

If I run the code in the morning then it takes too long to complete. If I then...

• This reply was modified 1 year, 5 months ago by as_1234.

All,

Thanks for your help.

I updated the statistics today and it seems to have fixed the issue. I will retry the code over the next few days to see if it...

All,

Thank you for your replies and suggestions. Sorry I haven't been back to this topic, I've had some other unplanned things to sort out. When I get back to it,...

• This reply was modified 1 year, 5 months ago by as_1234.

Hello,

Thank you for your help.

Are both servers running the same SQL Server version?

Are both databases using the same compatibility level?

Are both databases using the same transaction isolation level?

Are both...

All,

Firstly apologies for posting a question and not replying. That wasn't my intention. Some unexpected issues took my time.

The reason for renaming is that the backup will be picked up...

• This reply was modified 1 year, 11 months ago by as_1234.
• This reply was modified 1 year, 11 months ago by as_1234. Reason: Two typing errors corrected

All,

Thank you for your help and apologies for not replying. Another issue has taken my attention and I haven't had chance to reply.

I will reply to everyone's questions soon, probably...

All,

Thank you for your help.

I was able to capture all the information I need using Extended Events.

Just an update in case it's useful to anyone:

Scripting the database doesn't show any relevant permissions.

If you unversion the table the insert permission isn't shown in SSMS. If you re-version...

All,

Thanks for your replies.

Scripting out the database is a good idea, I'll try that.

I'll carry on investigating and if I find an answer or anything else that might be useful...

Sorry for taking so long to reply.

I don't get an error from that command.

I did some testing on a test system. I created a table as follows:

CREATE...

• This reply was modified 2 years, 4 months ago by as_1234.

I haven't tested this but maybe it requires one of the SSIS permission roles? I think this is the relevant documentation https://learn.microsoft.com/en-us/sql/integration-services/security/integration-services-roles-ssis-service?view=sql-server-ver15

As I said not tested and some of those...