We use a third party tool 'ControlM' to create jobs, execute them since we have hundreds of jobs which run everyday so the ask is to run the job...

The refinements continue!

WITH BaseData
AS (SELECT Data = ISNULL(t.Data, LAG(t.Data, 1, NULL) OVER (ORDER BY rt.yearwk))
          ,Week = rt.yearwk
...

Using aliases for table names is also a good idea. Shortens the query and improves readability.

Any one of those left joins could be increasing the number of rows returned beyond...

• This reply was modified 4 years ago by Phil Parkin.

Is it so difficult to use code blocks?

SELECT dbo.TrainingHeader.TrainingID
      ,dbo.TrainingHeader.TrainingNumber
      ,dbo.TrainingHeader.TrainingTypeID
      ,dbo.TrainingHeader.TrainingParticipantType
...

Try this (as my query gets uglier and uglier!):

WITH BaseData
AS (SELECT Data = ISNULL(t.Data, LAG(t.Data, 1, NULL) OVER (ORDER BY rt.yearwk))
      ...

One more question though. When we assign primary key to a column it creates clustered index by default on that column but I know that in future I will be...

Referring to staging tables as temporary tables is confusing!

For each of the staging tables, create a stored procedure. This stored proc should take care of 'changing the things you want'...

Are you inserting from the temporary tables to the same 'permanent' table in each case? Or are there different targets?

100,000,0 is not a number – please clarify.

I'd suggest doing the...

I'd be interested to hear your reasoning.

Here is a better version, which avoids the creation of a zero row when the preceding row was at the end of the month:

WITH BaseData
AS (SELECT Data...

Sorry for the delayed response. This gets closer, I think:

WITH BaseData
AS (SELECT Data = ISNULL(t.Data, LAG(t.Data, 1, NULL) OVER (ORDER BY rt.yearwk))
     ...

I recommend deploying your SSIS packages to SSISDB and running them from there.

Also, did you try this?

Try out the following code. You will see that the error generated in the first proc is captured in the second.

CREATE OR ALTER PROCEDURE dbo.ErrorTest1
AS
BEGIN
  ...

OK, so the process could be

1. Data Flow task

2. Copy file to <filename>_<invoiceId>.xlsx

3. Overwrite existing target file with empty except headings 'template' file

4. Process next InvoiceId

If the file rename task is added after the Data Flow, doesn't that achieve your aim?

If a new empty target Excel 'template file' has to be copied over after the...