OLE DB Destination Error

  • 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.

  • 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

  • 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

  • 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

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

    Thanks,

    Scott

  • 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

  • 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

  • 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

  • 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

  • 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.

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

    Thanks for the idea.

    Scott

  • 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,

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

    Thanks,

    Scott

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

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