You can use something like the below to indicate a total failure somewhere. At least in the testing phase.

begin transaction 

declare @rowcount int
Select @rowcount = count(*) from...

I can imagine :). I get those as well on posts I did a long time ago. Surprise.!

Note this is a ten year old post 🙂

I dont see the difficulty here though in theory....

is what the message indicates the problem is. If this is a table the column name could have been changed or dropped. Or if a view...

People have mentioned about formatting and casing consistency in code for readability. To me readability starts with knowing what each piece is doing to check for logic and not just...

If it reasonably takes an employee months to learn how a business unit works, it is not reasonable to expect a forum helper to pickup all details in one shot....

Glad to know that it worked for you. 
Cheers.

Using your data, I was able to successfully import to a table with date data type. Ahead of the import, I specified the three columns shown as [DT_dbDAte] types. I...

One thing I would add is instead of having a paycheck amount, having a paycheck ID that links to a paychecks table. This normalises the design better. The would be...

I have to wonder why you are changing the column values when copying the data to a new table. It definitely will not help with auditing if you need to...

One consideration if you had SSIS jobs running on the old servers to import data:
The folder structure on the new server may not match. That would require some adjustment...

I have seen this issue where a change of data type to datetime solves a insertion issue with date data. I just chalked it up to a Sql Server anomaly.