OLE DB Destination Error

Question

OLE DB Destination Error

Scott Arendt

SSCrazy Eights

Points: 8218
More actions
May 16, 2011 at 3:07 pm

#239748

I'm confused. I've got on OLE DB Destination set up to insert records into a table in an SSIS package.
The table contains three columns:
[LoginID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](60) NOT NULL,
[FullName] [varchar](118) NULL,
There Input Column for LoginID is set to <ignore>. That is, I am relying upon the fact that this is an IDENTITY column to set it's value. Yet, when it tries to do an insert, the insert fails with the following error.
"Cannot insert the value NULL into column 'LoginID', table 'SDE.dbo.SDELogin'; column does not allow nulls. INSERT fails."
I have used different Data access modes, to no avail.
If I do an INSERT through SSMS, then the record is inserted without error. Any help will be much appreciated.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

When using the Fast Load option, is the Keep Identity checkbox checked or not?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 2

Unchecked. If I understand the use of the option, I would only have it checked if I wanted to specify the contents of the column, correct?

Thanks,

Scott

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

Scott Arendt (5/17/2011)
Unchecked. If I understand the use of the option, I would only have it checked if I wanted to specify the contents of the column, correct?
Thanks,
Scott

Indeed.

You didn't map the identity column with another column in the mappings pane, did you?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 4

Yeah, just double checked and the identity column is set to <ignore> for mappings.

Thanks,

Scott

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

Can you generate the full CREATE TABLE statement of the table in SSMS. That way we can check the DDL to see if everything is OK there.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 6

Sure, no problem.

CREATE TABLE [dbo].[SDELogin](

[LoginID] [int] IDENTITY(1,1) NOT NULL,

[UserName] [varchar](60) NOT NULL,

[FullName] [varchar](118) NULL,

CONSTRAINT [PK_SDELogin] PRIMARY KEY CLUSTERED

(

[LoginID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Thanks,

Scott

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 7

That seems OK.

Well, I'm quite out of options. No triggers that are messing with the data?

Sorry, this all I can come up with without looking at the actual package.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 8

Nope. no triggers. I'll keep experimenting to get it to work.

Even though I still don't have it working, at least I know I am not overlooking something obvious.

Thanks for the help.

Regards,

Scott

Dan.Humphries SSChampion Points: 12125 More actions · Answer 9

not sure if this helps or not but just an observation. I have in the past run across things like this and when I look at the code page I notice somthing in the XML that for what ever reason does not seem to match what I have setup. For example I had an identity column that was for what ever reason picked up in the XML as a Varchar. not matter what I did it woul dnot change. I eventually changed the code page directly and that fixed the problem I was having. You might find something similiar.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 10

Interesting thought. I had not checked the XML previously, but the meta data has the correct data types.

Thanks for the idea.

Scott

rafavet SSC Rookie Points: 44 More actions · Answer 11

Hi there.

I tried something here and it worked when I selected 'delete rows from destination tables' instead of 'append rows to destination table'. The identity destination column is set on ignore and enable identity insert is unchecked also. See if it works.

Regards,

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 12

Is that an option in the wizard? I can't seem to find that option as part of OLE Destination Component.

Thanks,

Scott