I have a bunch of very basic DTS packages that I am converting to SSIS. They
simply truncate a table in the destination Db, then copy all rows from the
source. They run fine as DTS packages. When I re-design them in SSIS and run
them, I get an error stating:

"Cannot insert the value NULL intp column '<MyColumn>', table
'<MyDestinationTable>'; column does not allow nulls. INSERT fails.

While it is true that both the source and target tables dos not allow NULLs,
a quick scan of the source (SELECT COUNT(*) FROM <MySourceTable> WHERE
<MyColumn> IS NULL) reveals NO RECORDS.

In the DTS Package, the Transform Data Task Properties are set as follows on
the Options tab, SQL Server section:
Use fast Load: TRUE
Keep NULL values: FALSE
Check Constraints: FALSE
Table Lock: FALSE
Enable identity insert: FALSE
Always commit final batch: FLASE

I set up the SSIS with an OLE DB Destination as follows:
Data access node: Table or view - fast load
Keep identity: FALSE
Keep nulls: FALSE
Table Lock: FALSE
Check constraints: FALSE

Why would I be getting an error about "Cannot insert NULL . . . " when no
NULL values exits in the table? And if they did, what does the "Keep nulls"
switch do for me. I tried it both ways and get the same error. The
Destination table is an EXACT COPY of the source table so there should be no
data conversion or incompatibility issues.

Any suggestions?

Thanks in advance

Todd C

Hello Todd,


The "Keep Nulls" means exactly that. If you do not select this then Nulls
get converted to default values based on their source datatype.

Does it implode immediately. Another reason this may happen is if there is
no mapping between an input column and a destination column.


Allan

Thank you, Allen. Nice to hear from you again.

Turned out to be an un-mapped column in the data flow.

"Allan Mitchell" wrote: