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

Does the Excel file exist at the beginning of the process, or is it created during the process?

Now it is my turn not to understand. In your very first post, you said this:

I can get the data flow task to write data by per invoice Id into...

Here is one way. It converts the complete times to seconds and then subtracts one from the other.

((DT_I4) TOKEN( @[User::End_Time] , ":",1 )*3600 +(DT_I4) TOKEN( @[User::End_Time] ,...

I believe that this limit will have been imposed at source and therefore I suggest you get hold of whatever documentation or support is available for the particular REST API...

Using getdate() three times repeats exactly the same datetime three times. Notwithstanding that, this should work:

SELECT l.id
      ,l.step_name
    ...