Following on from Jeff's excellent advice, I'd like to add a little more. I'd suggest removing all spaces from your column names ... makes your code much less cumbersome because...

Declaring variables as Varchar without an explicit length is considered bad practice and can lead to unexpected results.

Use VARCHAR(n) (where n is the longest expected length) instead.

And if you're at all OCD with your database object names (or maybe you like to check everything in to source control & therefore avoid random names), consider using extended...

You need to put your defaults in parentheses, eg:

DEFAULT ('Y')

In stored procedure Procedure SP_FlagTests_CY, there is a reference to a column BaseSolvencyBuffer_CYMinus1, and that column cannot be found in the context of the query.

If you show us the content...

Not me.

Good thought. You are correct about my current use of the REST API. Will have a go at this at some point.

A new bump in the road with one person in the office. I'm working on a improving performance for a procedure and send it to code review with him....

Additionally, using options such as SET ROW COUNT are session wide and prone to unintentional mistakes if it is left active.  Additionally, if you are just playing in...

It's a warning, not an error. The help text provides a reasonable explanation as to why the warning has been generated.

The staging table / stored procedure route is the way I would go. T-SQL will likely blow away anything you can code in SSIS to do this.

You might find that you can expand the data ranges very rapidly in T-SQL, by joining to an appropriate calendar table or numbers table (it's hard to be sure what...

SET ROWCOUNT 10

SELECT ...

SET ROWCOUNT 0

If they really are NULL (and not just empty strings), simply add a WHERE clause to your query:

WHERE NOT (LoanID IsNull and LoanNum is NULL)

For each file Extract the patient Id from the file name Check whether the extracted patient Id matches a row in the SQL Server lookup table If a match...