I have a csv that gets imported into SQL server using BIDS\SSIS. One of the columns is called callerid which is just a phone number. The corresponding sql db column is numeric. Occasionally the callerid field is filled in as 'UNKNOWN' causing my import to break.
Is there a way to identify when this column is non-numeric, and if so, just set it to zero?
I would processed the file externally to replace incorrect value and then continue parsing it the way you do it when it has only correct entries.
If you run this command in Windows PowerShell it will replace all '"unknown"`
[io.file]::readalltext("C:\MyFilePath\MyFile.csv").replace("UNKNOWN","0")
This way you do not risk there is a non-numerical value in the phone number column.