How about something like this:

CREATE TABLE [#Temp]
(
[ID]  INT
, [Status_ID] INT
, [StartDate] DATE
, [EndDate]  DATE
);

INSERT INTO [#Temp]
SELECT
101
, 1
, '01/02/2020'
, '01/28/2020'
UNION ALL
SELECT
101
, 1
, '01/29/2020'
, '01/31/2020'
UNION ALL
SELECT
101
, 5
,...

This looks like a gaps and islands type problem to me.  I have a question about your data though - how many days after "EndDate" must a "StartDate" be to...

Did some testing and digging on my end and best I can figure out is that you would need to either:

A - do calculations in your SQL query

B - create...

• This reply was modified 3 years, 9 months ago by  Mr. Brian Gale. Reason: provided more details

Yeah, if you could calculate those values on the SQL side, this would be easy.  But I enjoy a challenge!

I see where it gets difficult though - the divisor is...

So, if I understand what you have properly, you don't have a "Fields!FTE.Value" that you can use to calculate the AVERAGE or the SUM?

I think the logic will be similar...

My approach then would be something completely different then.  If you are changing the back end disks I would do a completely different approach than what you are doing.  Might...

My process when i want to know things like that is to check the docs:

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtype?view=dotnet-plat-ext-3.1

If you see here, the CommandType default when not assigned a value in this...

Quick review of the code, the error isn't where you indicated.  How do I know - that exact line is assigning an integer a value.  That's it.  It is something...

What value do you expect to get when you sum up all of the FTE values?  19.525 is what you would get.  If you take the average of all of...

"no activity on the backup server" to me means "no backup jobs are running at this time from SQL", correct?  Are you 100% confident that your work will fit in...

Another thought - Visual Studio is 32-bit so the debugger is 32-bit.  SSIS is going to be 64 bit.  Just thinking of other things that may affect performance...

What happens to...

Is it the same account on the old system (NT Service\SQLSERVERAGENT) or do you have an actual account for it?

The proxy account will likely be your only option on the...

As a thought - can you log into the server as the SSIS Service account?  I am thinking about taking the opposite approach as Jeffery Williams (creating a proxy) and...

Another thought - do you NEED columnC in your FinalTable_bedeleted?  Probably speed things up a little bit by only pulling the ID's out of FinalTable, no?

Just thinking that 600 million...

I am pretty sure that SQL Prompt (at the moment) cannot colorize the tab based on the SQL login (Windows or SQL).  That is, if you log in as "sa"...