This sort of thing happens because data inside Excel is untyped. When you try to read a spreadsheet using SSIS, the OLEDB provider attempts to turned untyped data into typed data. To do this it reads the first "Few" rows (configurable by updated the registry on each machine that will execute the SSIS package) and based on what is in each column, it will make a decision about the datatype that is applied to the entire column. So, if the first few columns contain numbers, it may determine that the appropriate data type is a 4 byte integer. This metadata is then given to SSIS. If SSIS is happy with it (i.e. the meta data is the same as when the package was designed), then you can proceed to the next step and read all of the data in the spreadsheet. When it encounters the value "str", instead of throwing an exception, the OLE DB provider simply returns NULL.

To change the number of rows used, search the registry for "TypeGuessRows". From memory the default value is 8. AND, there may be several entries - and not all apply to excel (the name of the registry key is pretty obvious, though)

You can change the connection string so that the OLE DB provider treats everything as text. Have a look at https://www.connectionstrings.com/excel/ - this gives more info on this.