This article from Brent Ozar might help

https://www.brentozar.com/archive/2018/09/sql-server-2019-faster-table-variables-and-new-parameter-sniffing-issues/%5B/quote%5D

I had read that prior to posting but I don't think that's what is happening here. The TVP row estimates were...

Realistically at this point, I'm just trying to gather a better understanding of what is going on here for my own sanity.  If the Execution plan shows...

• This reply was modified 6 months ago by  planetmatt.

I'm not sure if this will fix it but the first thing I would try is to make sure the statistics have been updated:

UPDATE STATISTICS dbo.mytable...

The temp table works even without an index.

However, it is not my code. This is code executed via DynamicSQL (being dynamic or static doesnt change performance) from a 3rd party...

No, it's not a failover issue.  The AG is set to manual failover and has not been failed over between the job fail events.  The job/package is running consistently from...

This script works perfectly for recreating existing environments.

Is there a similar script that can automate or script out the mapping of package parameters to the environment variables?

We only have one server with one catalogue.

We use it to override settings that may come from deployments. New project -> Configure -> Link to environment. You...

We only have one server with one catalogue.

We use it to override settings that may come from deployments. New project -> Configure -> Link to environment. You may not...

To handle the scenario where additional charge columns could be added to the table, "charge6, charge7" etc, you could do the below to dynamically unpivot any columns that match your...

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

The reference table makes little sense because as I understand it, each row represents a target table.  You can't really feed that into a single Dataflow because each table will...

I don't really understand what this reference table is doing.

To load the files, can't you have 2 x ForEach Loops, with their collections set to each set of files (wildcard...

Hi Jeff,

Can you...

If it is in a for each loop,  you can make it try again on failure.

There are instructions here: https://www.mssqltips.com/sqlservertip/3575/continue-a-foreach-loop-after-an-error-in-a-sql-server-integration-services-package/

The key points are:

1. Set MaximumErrorCount property of...

I've just changed my staging folder parameter to point at my local C drive and so far it hasn't failed once even with me removing the wait script command.

Definitely looking...

The error always occurs in the Excel Data Source at the start of the data flow.  Only ever there, but which file it fails on is random.   Often it will...