Without an expected output, butusing a previous post, this is a guess:
SELECT DueDate,
       ProductName, ProductDescription, ProductGroup,
       SalesOrderNumber,
       --FIRST_VALUE(QtyInStock)...

The error message is telling you the problem here, the user/login the report is running under is being denied permission to select from the object "ServiceRequired" on the database "XXXXX".

If you're simply changing the connection string from one server to the other, then double hopping won't be a problem. Your connection would be going directly to the other server...

You can much more easily get the data out of your XML by using XQuery on your table. So the below would be used after you insert your data into...

Personally, I find the easiest way is to insert the data into a staging table, and then use a MERGE statement, or a INSERT/UPDATE statement. SSIS does have tools to...

What have you tried so far? There could be a few answers to this, but it also depends on your data(for example, will it always only have a maximum of...

Not much to go on, but perhaps:
SELECT *,
      CASE WHEN LongRunN2 IS NULL THEN NULL
            ELSE (SELECT COUNT(*)