null value error

Customer Support Database Support
1

i am trying to import into mssql db a text delimited file. When it reaches the last row I get error “cannot insert the value NULL into column ‘CustomerID’, table “table”; column does not allow nulls.”

I have removed all white spaces in the text file. And Ive made CustomerID an autonumber seed 2 increment 1. Im at the end of my rope. :frowning: also I have mapped the fields in transform appropriately I believe. Thanks in advanced.

2

Datatype: int
Identity: Yes
Identity Seed: 1
Identity Increment: 1

is the default created by the upsize wizard in Access when a field is AutoNumber. It should work fine.. but maybe that’s not the cause of your problem.. try to remove the last line and see what happens

3

[QUOTE=atomi]
i am trying to import into mssql db a text delimited file. When it reaches the last row I get error “cannot insert the value NULL into column ‘CustomerID’, table “table”; column does not allow nulls.”

I have removed all white spaces in the text file. And Ive made CustomerID an autonumber seed 2 increment 1. Im at the end of my rope. :frowning: also I have mapped the fields in transform appropriately I believe. Thanks in advanced.
[/QUOTE]

Make sure there isn’t a blank line at the end of the text file. If there is not, try to remove the carriage return/line feed (newline) from the last record and try the import again.

riley

4

thanks for the responses. Im glad to be a member of jodohost community.

I did remove the last line at the end of the text file I have set the text qualifier to none : Row delimiter to {CR}{LF} : skip rows unticked : first row has column names unticked : the column delimiter to other : {space} : Append rows to destination table ticked : enable identity insert ticked : set CustomerID field source to : and the field destination to Col001 . YET when I get to the last row I get this error “cannot insert the value NULL into column ‘CustomerID’, table “table”; column does not allow nulls.”

Thanks again in advance. Here is a screenshot of my error http://www.pixpond.com/xx/jodoshared2.jpg

5

Iam thinking I might use this transform script to skip rows

Function Main()
If IsNull(DTSSource(“Col001”)) Then
Main = DTSTransformStat_SkipRow
Else

DTSDestination("EmailAddress") = DTSSource("Col001")
Main = DTSTransformStat_OK

End If

End Function

However I get an identity error saying im attempting to add NULLS to the first row. I have been stumped for a few hours now. Oh well>

6

untick ID solved the problem