SSIS tries to insert NULL value into a destination column ignored in the mapping.
Hello everyone, I am having a little problem with a simple package and I do not know if this is a known issue or that I am missing something. I have a data flow task, a simple one, with an oledb source pulling data, using a select statement, from a sql server 2005 instance, andan ole db destination pointing to a table in a sql server 2000 instance.Both intances are standard edition. The table in the destination has a column which allow null values and has also a default constraint (getdate()), and this column is not present in the source. When I map the columns in the destination, I leave this columnas "ignore", not being mapped to any column from the source. The problem is that when I execute the task, SSIS is trying to insert NULL value into this column, so the package fail with the error "can not insert NULL value into column myColumn". I wonder why is it trying to insert NULL value if the column is not mapped to any column from the source. Is this a known issue or I am nissing something in the settings? If the destination table has rowversion or identity columns, there is no problem ar all. I ignore those columns in the mapping and SQL Server feeds them as expected. Thanks in advance, Alejandro Mesa
January 13th, 2008 1:21am
Hi Alejandro,Nice to see you here.I think you are missing something. In SSIS, if you don't map (ignore) the insert will not consider that column, period. If the column is not null and you have default value defined; then the default value should kick in.What version and service level pack are you running ?
January 13th, 2008 10:02pm
Hi Rafael, Thanks for jumping in. Sorry I forgot to mention the version and sp level of the three instances involved. It seems that it was my mistake, because I checked the option "keep nulls" in the ole db editor, which is puching NULL values for the columns not mapped. See you around, AMB
January 14th, 2008 7:54pm
Hi, I experienced the same problem. My Instance is SQL 2005 SP2. I'm not sure if unchecking the option "keep nulls" in the ole db editor is the right way to achieve the aim, it is just a workaround. What if I have a NULL value in a column which is mapped to the target table? An empty string would be written to the target column in the target table for this row, which is not the right thing to do. I think this is another bug in SSIS. Best regards, Stefoon
January 15th, 2008 5:16pm
Hi Stefoon, You are right, it is a workaround but not the solution. I am risking to use default values instead the NULL values coming from the source. SSIS behaves weird if the column is not nullable, has a default constraint and is not mapped to any column from the source. I did this test: destination (SS 2005 SP2) CREATE TABLE dbo.t1 ( c1 DATETIME NOT NULL DEFAULT(GETDATE()), c2 VARCHAR(25) NULL DEFAULT('SSIS') ) GO source: SELECT NULL as c2 I mapped just column [c2]. If I execute the package without checking "keep nulls", the result is: c1 c2 2008-01-15 18:04:56.183 SSIS Notice that it did not keep the NULL value for [c2], as expected,and also used the default value for [c1], which is not mapped. if I check "keep nulls" in order to keep the NULL value for [c2], expecting to push the NULL value to [c2] but using the default constraint for [c1] because it is not mapped, then I get the following error. [OLE DB Destination ] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'c1', table 'Northwind.dbo.t1'; column does not allow nulls. INSERT fails.". SSIS is pushing a NULL value to [c1], not the default value, so it is not ignoring it. That is the bug, at least for me. Also, the description from BOL is poor or not clear. Keep nulls Specify whether to copy null values when data is loaded. This property is available only with the fast load option. The default value of this property is false. If you check the description for the utility BCP, you can read: -k Specifies that empty columns should retain a null value during the operation, rather than have any default values for the columns inserted. Regards, Alejandro Mesa
January 16th, 2008 2:17am
Hi, maybe this would be worth an entry in http://connect.microsoft.com. Best regards, Stefoon
January 16th, 2008 12:18pm
hi, got the same problem with a ole db destination. used instead a sql server destination and it worked. any ideas why?
January 17th, 2008 1:20pm
Alejandro, that behavior seems correct to me. If you're going to "keep nulls" then you need to map all of the columns, or it will implicitly map them for you when doing the insert. If you don't have a column mapped to c1, when choosing to keep nulls, the bulk insert routine will, for empty columns, "retain a null value during the operation." Hence this is valid behavior.Don't use the fast load option, and try it without and see what happens.
January 17th, 2008 4:52pm
I was having trouble with an insert where a target datetime column was non-nullable but had a getdate() default value and there is no input value for that column (non needed). Elsewhere I saw it suggested to not run in fastmode. I tried it and it worked. Not sure why. I certainly don't know what isdifferent (except my package works now).
August 15th, 2008 7:31pm
As of today (several years on) in version 10.0.2775 (x64) this issue remains - I guess that means it's a feature. It is a braindead implementation and about what I have come to expect from SQLServer engineering. Ignore means ignore, and unmapped means unmapped. How hard can it be? Bruce's example is very very typical of load situations where timestamps are typically used for synchronisation purposes. I have a whole swag of these, all broken by this "feature" of SSIS after conversion from DTS It seems that using "OpenRowSet" will load the rows and the default timestamp column will get the correct value, but say bye bye to the facility to specifically recognise zero length strings as NULLs. More coding needed to overcome the fact that SQLEngineering can't. "Expect nothing, and be unsurprised when you get it it" cheers philip
December 7th, 2010 7:47am