Here is a simple C# function which returns true if a file is locked, false otherwise.
    private bool FileIsLocked(string filename, FileAccess file_access)
   ...

I'm sure that there are good reasons for you choosing to do this the way you have, but I do have a few questions / comments.

• DDL operations (CREATE TABLE...

SQL Prompt can do all of the terminator legwork for you.

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