No, the script that puts the file into the folder from which the data flow loads it is simply testing for a folder existence, creating a folder if not present,...

Does it with a 10 second delay.

Oh wait, I think I only paused for 5ms and not 5secs.  Will test again.

The file is moved from an input to a staging folder

A Data Flow (the bit that randomly fails) the reads the file into SQL

The file is then moved from staging...

Then can't you just change the search string to:

DECLARE @Find VARCHAR(MAX)='PageDefintion>'

?

You don't need all those REPLACE functions.

You can just do

DECLARE @Find VARCHAR(MAX)='PageDefintion'  

SELECT 
Detail_Form
,LEN(@Find) ,(LEN(Detail_Form)-LEN(REPLACE(Detail_Form,@Find,'')))/LEN(@Find)  AS OccurrenceCount  
FROM dbo.Table1

Thanks Steve, understood.

You can replace each of the 2 CTEs with Derived Tables still using Row_Number() to create your surrogate key

SELECT C1.Id
,C1.SinkCreatedOn
,C1.SinkModifiedOn
,C1.statecode
,C1.statuscode,
    CASE WHEN ISNULL(CAST(C1.ts_primarysecondaryfocus AS...

The input from Excel are key value pair(s)?  Suppose the inputs were inserted into a temporary SQL Server table.  Would/could it look something like this?

create table...

For 1, I tried a CTE to create a Month lookup table.  No hardcoded string values

;WITH CTE_Months

AS

(

SELECT GETDATE() AS TheDate, LEFT(DATENAME(mm,GETDATE()),3) AS [MonthAbr],MONTH(GETDATE()) AS MonthOrder

UNION ALL

SELECT DATEADD(mm,1,TheDate),LEFT(DATENAME(mm,DATEADD(mm,1,TheDate)),3),MONTH(DATEADD(mm,1,TheDate))

FROM CTE_Months

WHERE...

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

For 1, I tried a CTE to create a Month lookup table.  No hardcoded string values

;WITH CTE_Months
AS
(
SELECT GETDATE() AS TheDate, LEFT(DATENAME(mm,GETDATE()),3) AS [MonthAbr],MONTH(GETDATE()) AS MonthOrder
UNION ALL
SELECT DATEADD(mm,1,TheDate),LEFT(DATENAME(mm,DATEADD(mm,1,TheDate)),3),MONTH(DATEADD(mm,1,TheDate))
FROM...

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

I have now restored the backup to completely different hardware and done a CheckDB and it was fine so the issue is very much with the Reporting DB MDF file...

For reference, I've been referred to this article that infers that yes, a backup that is verified, check summed, and can be restored without error, might still be corrupt which...

Interesting, and sadly, I actually cannot remember. I did drop the DB but can't remember if that was the first or second restore.

If we assume I didn't drop it and...

Hi Jeffrey, this wasn't born out in testing.

Changing the File System Task from Move File to Copy File has the same problem

Also, manually copying manually moving the file didn't have...