I can summarise the above as: "I have some data spread across multiple tables. I've written a cursor which can combine the data, but it's slow and does not work...

Here's something more readable – with > 5000 points, you should know how to use IF tags. Perhaps you would also include desired results, based on your sample data?

;with myTable (StartDate ,EndDate) AS

That semicolon 'initiator' is there just to wind us up, right? 🙂

As you are using SSIS, you should not need to use linked servers to do this.

The direct pull of data from the remote server to the local...

My apologies ... I read your initial post on my phone and missed the important details.

What is your question?

Look at the query which is generating the error.
Look at the columns whose target is an INT.
Of...

This is the same number, formatted as Scientific (left) and General (right).