Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cannot load into table having identity column - INSERT fails Expand / Collapse
Author
Message
Posted Saturday, January 11, 2014 12:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
Very odd behavior in BIDS 2010. Why is SSIS changing removing the IDENTITY property of the destination table?

I have a Simple SSIS flow: Flat File Connection->OLE DB Destination. I want to load flat file data into a table that accommodates all columns and populates an identity column.
The create statement I used for the SQL Server destination table is

create table stage_it_emp(
eid int identity (1,1) primary key,
ename varchar(20) null,
dept varchar(10) null,
salary int not null
)

and if I run this SQL Statement, it auto populates the identity column, and inserts values for the other three, as expected.

insert into stage_it_emp
values
('Bossy', 'IT', 8000);


I can do as many inserts as I like in SQL, but when I run SSIS the insert fails with this error

Error description:

"Cannot insert the value NULL into column 'eid', table 'Demo.dbo.stage_it_emp'; column does not allow nulls. INSERT fails.".

THE MOST ODD THING is that after running the SSIS package, I look at the structure of the destination table and it has lost it's IDENTITY structure! Here's the resulting structure.

CREATE TABLE [dbo].[stage_it_emp](
[eid] [int] NOT NULL,
[ename] [varchar](20) NULL,
[dept] [varchar](10) NULL,
[salary] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[eid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Keep NULLs in OleDB editor is off.

What data flow property do I need to adjust to successfully load 3 columns into a 4 column table, where the additional column is identity?
Post #1530019
Posted Saturday, January 11, 2014 2:25 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:30 PM
Points: 403, Visits: 303
I tried to replicate the issue as you described and did not have the problem using 2012. Keep identity should be unchecked and is by default.

Is a field mapped to the EID column in the Mappings section?

By chance did you select the name of the table then click the new button to the right (doubtful)?

Mike


Mike

How to Post Performance Problems

How to Post Best Practices
Post #1530064
Posted Saturday, January 11, 2014 10:11 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
Montana Life (1/11/2014)
I tried to replicate the issue as you described and did not have the problem using 2012. Keep identity should be unchecked and is by default.
Yes, iidentity is also unchecked.

Is a field mapped to the EID column in the Mappings section?
No, the field is not mapped to EID column as there is no field. From the flat file side it is <ignore>.

By chance did you select the name of the table then click the new button to the right (doubtful)?
I am not sure what this is asking.

Mike


Mike, thanks for taking a look at this, and lending your mind to my problem, plus testing it in 2012. I hope my install of SQL Server Data Tools aka BIDS 2012, goes well.
Post #1530076
Posted Sunday, January 12, 2014 3:26 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
I installed BIDS 2012 but I think it just integrates Visual Studio 2010 within it's shell, and in my case it detects my 2010 packages but when I try to open them I get error:
Unsupported. This version of Vicual Studio does not have the following project types installed or does not support them:
..\ForeachLoopPractice\SSISPractice.dtproj

I would be happy to recreate the package in 2012, but don't know how to open a brand new Integration Services Project. I click on SQL Server Template, then SQL Server Database Project, and it takes me to a database design window. No Integration Services in sight.

So if someone else has insight into this crazy problem, I'd love to read it.
Post #1530156
Posted Sunday, January 12, 2014 10:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
polkadot (1/12/2014)
I installed BIDS 2012 but I think it just integrates Visual Studio 2010 within it's shell, and in my case it detects my 2010 packages but when I try to open them I get error:
Unsupported. This version of Vicual Studio does not have the following project types installed or does not support them:
..\ForeachLoopPractice\SSISPractice.dtproj

I would be happy to recreate the package in 2012, but don't know how to open a brand new Integration Services Project. I click on SQL Server Template, then SQL Server Database Project, and it takes me to a database design window. No Integration Services in sight.

So if someone else has insight into this crazy problem, I'd love to read it.


Your error message suggests that you do not have the correct (or any) version of SQL Server Data Tools (SSDT) installed - please check that. It's not called 'BIDS' any more, by the way.

But you should not need to upgrade to 2012 to resolve this problem. Montana Life has asked two questions, both of which are relevant and both of which you seemed to ignore for some reason. Please answer them.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1530177
Posted Monday, January 13, 2014 9:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 2,059, Visits: 1,431
Phil Parkin (1/12/2014)
Your error message suggests that you do not have the correct (or any) version of SQL Server Data Tools (SSDT) installed - please check that. It's not called 'BIDS' any more, by the way.


If you want to be able to create SSIS, SSRS and SSAS projects you'll need SSDT for BI as opposed to SSDT which is just for Database Projects. It's very confusing and who knows what Microsoft were thinking when they came up with the naming conventions, but SSDT-BI (SSDTBI?) is available as an installable feature in the SQL Server install media as well as a separate download here

Regards
Lempster
Post #1530366
Posted Monday, January 13, 2014 10:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
Lempster (1/13/2014)
Phil Parkin (1/12/2014)
Your error message suggests that you do not have the correct (or any) version of SQL Server Data Tools (SSDT) installed - please check that. It's not called 'BIDS' any more, by the way.


If you want to be able to create SSIS, SSRS and SSAS projects you'll need SSDT for BI as opposed to SSDT which is just for Database Projects. It's very confusing and who knows what Microsoft were thinking when they came up with the naming conventions, but SSDT-BI (SSDTBI?) is available as an installable feature in the SQL Server install media as well as a separate download here

Regards
Lempster


Thanks!



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1530368
Posted Tuesday, January 14, 2014 9:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
I did answer Montana Life's questions. Not like me not to. Oh Oh Phil, Do you need to have your eyes checked?

For now I've removed the EID field in destination table so I can continue with my goal of learning a few more transforms in 2010. Thanks everyone for advice on 2012 install, which I do need to tackle, but was for the purposes of the question a workaround. I read that 2012 isn't really bringing forward any new integrations services functionality, is rather a shell for 2010 anyway, so i would probably still face that strange issue.
Post #1530766
Posted Tuesday, January 14, 2014 10:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
polkadot (1/14/2014)
I did answer Montana Life's questions. Not like me not to. Oh Oh Phil, Do you need to have your eyes checked?

For now I've removed the EID field in destination table so I can continue with my goal of learning a few more transforms in 2010. Thanks everyone for advice on 2012 install, which I do need to tackle, but was for the purposes of the question a workaround. I read that 2012 isn't really bringing forward any new integrations services functionality, is rather a shell for 2010 anyway, so i would probably still face that strange issue.


I see that you tricked me by adding your answers to the quoted text ... missed that. I'll bet I am not alone though!

I'm off to see the optician right away!



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1530775
Posted Tuesday, January 14, 2014 5:52 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
Yes, I selected the quote tag when responding, and I guess I should have added more start and end tags for each section I was replying to.
Naw, don't worry, you're eyes are fine. I see your point.
Post #1530921
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse