I have been importing from a table of 547,000+ records from a text file source. In a field with 2 character spaces, I have trimmed the empty characters in Excel before saving as a TXT file. So, this means there are 547,000+ records with some with 2 characters in this field and some that are NULL. When the import completes to SQL Server 2014 with the data type set for this field to varchar(50), it appears the database engine adds those 2 character spaces back. Is this true? I know this because when a query is run with the WHERE clause condition to IS NULL, the results are 0. This means that no records in this field are found as NULL. Which implies that fields that were trimmed have had their character spaces added back.
How are you importing this data? If you are using SSIS and did not check the box 'Retain null values from the source as null values in the data flow' then SSIS will pass those through as empty strings.
Using SSIS you can also add the derived column transformation - or the data conversion transformation to convert the empty strings into the appropriate NULL value for the defined data types of those columns.
One thing to be aware of is that NULL values will add additional coding issues when inserting/updating data from the file. If you are processing the data and identifying rows that are new and rows that need to be updated you have to account for NULL values in the source and target - e.g. WHERE coalesce(source.col1, '') <> coalesce(target.col1, '').
No it's not thru ssis. I'm helping someone at work. I just convinced him to download and install ssis. Thx anyway
If it is not through SSIS - then how are you importing this data? How you handle null/empty values will depend on the tool being used.
It was being done as a direct import thru the SSMS import wizard, but I will install SSDT tools when my colleague returns from vacation and do it thru SSIS. This problem is now solved. Thx for your advice.
The Import/Export Wizard generates an SSIS Package. You can save the package to disk when the wizard creates it. In SSDT you can create a new project and delete the default Package.dtsx, right click Packages, choose Existing Package, select the file the wizard created. Now you can edit the package and figure out why the data isn't correct..