January 11, 2014 at 12:27 am
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?
--Quote me
January 11, 2014 at 2:25 pm
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
January 11, 2014 at 10:11 pm
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.
--Quote me
January 12, 2014 at 3:26 pm
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.
--Quote me
January 12, 2014 at 10:43 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 13, 2014 at 9:52 am
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
January 13, 2014 at 10:05 am
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!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 14, 2014 at 9:57 am
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.
--Quote me
January 14, 2014 at 10:24 am
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! 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 14, 2014 at 5:52 pm
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.
--Quote me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply