@DesNorton, this sounds like a good solution, well done.

Can you expand a little on what the 'sessions' are?

How should one 'session' know what the other sessions are doing (so that they don't have any overlapping or missed rows))?

How...

In this case outer or cross is same thing - I do tend to use outer apply (left outer) instead of cross apply (inner join)

I have no idea whether this...

Can you expand on what you mean by "but this doesn't produce the desired result"?

Note also that a unique constraint is not quite the same as a unique index.

Frederico .... maybe you're onto something.

Your query has multiple references to ftbid01 which obviously should be to 01, 02, 03, ...

The outer apply could be a cross apply, or do...

I don't see anything that would require this query to be written dynamically. This should work:

UPDATE a
SET fta1 = 'ftbid' + CAST(DAY(b.orderdate) AS VARCHAR(2))
FROM tba a
 ...

The service account for which service, exactly? Or is the same service account running multiple services?

I've done this before, using a Script Component source.

You'll find articles online from others who have done this before. Here is an example.

Sorry, I thought you were referring to sys.objects and sys.key_constraints.

TVFs obviously don't have PKs. Not sure why it...

They're Views, not TVFs. Views don't have PKs.

This table structure is, and I don't use the word lightly, horrendous.

Is it set in stone, or is there the potential to remodel this into something which follows basic relational...

How do I fix this?

1. Find out which of the columns you are referencing is of type SQL_VARIANT
2. Use the CONVERT function to convert them to VARCHAR

And that should be...

I am pretty sure that if the SSIS service is not running, you can't do most SSIS related tasks such as deploying to an SSIS catalog.

I am a little...

I don't know EF well enough to comment on that, unfortunately.

Using more appropriate data types:

DECLARE @hFrom TINYINT = 9
,       @mFrom TINYINT = 0
,       @hTo ...