OK, created version 2 of the query, using a DateTime2 column from the standard MDS database:
WITH SomeData
AS
(
  SELECT *
  FROM
    (

I created a somewhat similar 'fail query' to yours, but which should run on others' systems. Here it is:
WITH SomeData
AS
(
  SELECT *
  FROM
    (
     SELECT
       *
     ,    rn...

In my opinion, embedding complex SQL logic in an SSIS package is a bad idea. Because when it comes to considering changes to a database (for example refactoring, new features,...