July 11, 2014 at 7:12 am
I have created a table and I have included a check constraint, but it doesn't seem to be working the way I thought it would. Here is the code that I have built on the table. I thought this was supposed to only allow records that have School_Year_Key=10 into the table and any records that has a different value would not load into the target table.
ALTER TABLE [dbo].[APSB_FACT_SYK10] WITH NOCHECK ADD CONSTRAINT [CK_SCHOOL_YEAR_KEY_10] CHECK (([SCHOOL_YEAR_KEY]=(10)))
GO
ALTER TABLE [dbo].[APSB_FACT_SYK10] CHECK CONSTRAINT [CK_SCHOOL_YEAR_KEY_10]
GO
Can someone please let me know what I am doing wrong. I tried testing this by running an ETL workflow that has only records where school_year_key=9. I was expecting no records to make it to the target table, but instead all the records loaded.
Thanks for the help.
July 11, 2014 at 7:15 am
Did you use SSIS to load the data? If yes, are check constraints enabled in the OLE DB Destination?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 11, 2014 at 7:22 am
Koen Verbeeck (7/11/2014)
Did you use SSIS to load the data? If yes, are check constraints enabled in the OLE DB Destination?
I am using SSIS and I literally just saw that check box. To prevent the dataflow from failing, do I just update the error output to "Ignore Failure"?
Thanks for the quick feedback Koen.
July 12, 2014 at 10:01 am
skaggs.andrew (7/11/2014)
Koen Verbeeck (7/11/2014)
Did you use SSIS to load the data? If yes, are check constraints enabled in the OLE DB Destination?I am using SSIS and I literally just saw that check box. To prevent the dataflow from failing, do I just update the error output to "Ignore Failure"?
Yes. Or you can redirect it to an error file.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply