SQLServerCentral.com / Integration Services / Data Warehousing / Cannot load into table having identity column - INSERT fails / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 12:26:23 GMT20RE: Cannot load into table having identity column - INSERT failshttp://www.sqlservercentral.com/Forums/Topic1530019-364-1.aspxYes, 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.Tue, 14 Jan 2014 17:52:32 GMTpolkadotRE: Cannot load into table having identity column - INSERT failshttp://www.sqlservercentral.com/Forums/Topic1530019-364-1.aspx[quote][b]polkadot (1/14/2014)[/b][hr]I did answer Montana Life's questions. Not like me not to. Oh Oh Phil, Do you need to have your eyes checked? :-PFor 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.[/quote]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! :-)Tue, 14 Jan 2014 10:23:59 GMTPhil ParkinRE: Cannot load into table having identity column - INSERT failshttp://www.sqlservercentral.com/Forums/Topic1530019-364-1.aspxI did answer Montana Life's questions. Not like me not to. Oh Oh Phil, Do you need to have your eyes checked? :-PFor 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.Tue, 14 Jan 2014 09:57:27 GMTpolkadotRE: Cannot load into table having identity column - INSERT failshttp://www.sqlservercentral.com/Forums/Topic1530019-364-1.aspx[quote][b]Lempster (1/13/2014)[/b][hr][quote][b]Phil Parkin (1/12/2014)[/b]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.[/quote]If you want to be able to create SSIS, SSRS and SSAS projects you'll need [b]SSDT for BI [/b]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 [url=http://www.microsoft.com/en-us/download/details.aspx?id=36843]here[/url]RegardsLempster[/quote]Thanks!Mon, 13 Jan 2014 10:05:00 GMTPhil ParkinRE: Cannot load into table having identity column - INSERT failshttp://www.sqlservercentral.com/Forums/Topic1530019-364-1.aspx[quote][b]Phil Parkin (1/12/2014)[/b]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.[/quote]If you want to be able to create SSIS, SSRS and SSAS projects you'll need [b]SSDT for BI [/b]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 [url=http://www.microsoft.com/en-us/download/details.aspx?id=36843]here[/url]RegardsLempsterMon, 13 Jan 2014 09:52:03 GMTLempsterRE: Cannot load into table having identity column - INSERT failshttp://www.sqlservercentral.com/Forums/Topic1530019-364-1.aspx[quote][b]polkadot (1/12/2014)[/b][hr]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.dtprojI 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 &#119;indow. No Integration Services in sight.So if someone else has insight into this crazy problem, I'd love to read it.[/quote]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.Sun, 12 Jan 2014 22:43:37 GMTPhil ParkinRE: Cannot load into table having identity column - INSERT failshttp://www.sqlservercentral.com/Forums/Topic1530019-364-1.aspxI 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.dtprojI 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 &#119;indow. No Integration Services in sight.So if someone else has insight into this crazy problem, I'd love to read it.Sun, 12 Jan 2014 15:26:03 GMTpolkadotRE: Cannot load into table having identity column - INSERT failshttp://www.sqlservercentral.com/Forums/Topic1530019-364-1.aspx[quote][b]Montana Life (1/11/2014)[/b][hr]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 &lt;ignore&gt;.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[/quote]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.Sat, 11 Jan 2014 22:11:55 GMTpolkadotRE: Cannot load into table having identity column - INSERT failshttp://www.sqlservercentral.com/Forums/Topic1530019-364-1.aspxI 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)? MikeSat, 11 Jan 2014 14:25:47 GMTMontana LifeCannot load into table having identity column - INSERT failshttp://www.sqlservercentral.com/Forums/Topic1530019-364-1.aspxVery 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-&gt;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_empvalues('Bossy', 'IT', 8000);I can do as many inserts as I like in SQL, but when I run SSIS the insert fails with this errorError 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?Sat, 11 Jan 2014 00:27:48 GMTpolkadot