SQL Server Performance Forum – Threads Archive

handling null values in text transforms

Folks,
I have a dts package that copies the contents of a number of text files into a sql server table using transform tasks.
The destination table has a non-clustered index on the serial_number(string) column with a default value of ‘ ‘.
the text information is collected using a barcode reader and sometimes they get the entry wrong and the serial_number value is entered as null. I got this error when th package ran last night..
(Microsoft Data Transformation Services (DTS) Data Pump (80004005): Insert error, column 13 (‘SERIAL NUMBER’, DBTYPE_STR), status 10: Integrity violation; attempt to insert NULL data or data which violates constraints.) (Microsoft OLE DB Provider for SQL Server (80004005): Unspecified error)
Turns out one of the rows had a blank serial_number. I thought that if you had option "keep null values" un-checked (and had a default value set on the column in the destination table) the transformation would just ignore the null values from the source side. Im getting around this problem by allowing a "max error count" of 5 as these null values are rare, but its not a fix Im too comfortable with.
I dont want to have to go droping and creating indexes and constraints as part of the package if I can help it.
Any ideas?
Cheers
Mick

---

Solution 1-
Change the column in the destination to accept null values and after each import check for such values. Solution 2-
If you ar using query logig for selecting data from source table, check for null values before execution and change them to ” which is the defualt in destination table. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

---

]]>