I'm more curious as to whether it really matters or not.   SQL 2005 was designed more than a decade ago, and the 2014 version of SQL Server made changes to...

Simple.   Quote marks (') in the data.  If the column is coming in as a string, you'll need to add a REPLACE function to the transformation to replace all single...

"Not able to connect" could be lots of things.   First, what error do you get?   Second, what kind of connection, exactly, are you trying to make?   Please be more detailed...

Did you try it?   And what results did it produce?  Notice that you are using INNER JOIN between your CSI cte and your NCI cte, so a given table would...

Have you heard of "global" temp tables?   The names all start with ##.    Google that and you'll likely find the scope information you seek.

To at least get you the Row Count, SSIS has traditionally had a  Row Count transformation that can provide that information into a package variable of your choice.   Having to...