I am having a fairly large issue in trying to load from a csv file. The normal load process isn't suitable as the format of the csv file may change. The change is that the columns may not always exist. (Please advise if I have missed something in the normal load solution).
So to get around this problem I reverted to using a script. The scripting is fine, but that leads to two further issues.
- There is no 64 bit method of reading from the csv (Please correct me if I am wrong). The solution must work in the design tool as well as on the destination.
- Reading the data causes out of memory exception. This occurs whilst testing through SSDT, but also on the destination server (although had to switch to 32 bit to get around the second issue)
The file isn't particularly large 1Gb approx., a few million rows.
Does anyone have a solution that would allow me to read a csv file that loads into a staging table (format of staging subtly different to the source column)?