I am trying to load a csv file into a SQL database. The csv file has been created from another database using the Save Results As csv option, and therefore the string "NULL" appears in many columns of the csv file which correspond to fields that should not contain strings, eg int or datetime fields. Although SSIS would seem to be the right tool for helping me import the csv data into a new SQL table, the "NULL" issue (amongst others) has caused me so many problems that I have given up on using SSIS.
I am therefore now trying an alternative two-step approach, which is to:
1. Use BULK INSERT to load all the data into a SQL table that is constructed using VARCHAR(MAX) as the data type for all fields. This has been achieved.
2. Use a method to convert the contents of the VARCHAR table to a second table that contains the same field names, but with the fields having the correct data type, Ocurrences of "NULL" should be entered as NULL in the destination data fields. The question is, how should I achieve this second step?