I am using SSIS 2008 R2. I developed a package which selects all rows into an existing table. The way I do this is I delete all of the records from the destination table. But when I attempt to write to this table I get this error: cannot insert duplicate key row in object Client_Information with unique index 'IX_Client_Demographics_Unique' The trick is that I am not supposed to drop and recreate this table. And my supervisor prefers that I not drop any of the indices or constraints either. Also, I am not writing to this client_information_id field. client_information_id is a uniqueidentifier datatype which allows NULLS. USE [generic_conversion] GO /****** Object: Index [IX_Client_Demographics_Unique] Script Date: 08/06/2012 13:10:03 ******/ CREATE UNIQUE NONCLUSTERED INDEX [IX_Client_Demographics_Unique] ON [dbo].[Client_Information] ( [client_information_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO This is my source script that I am using to select which records I add into the Client_Information table: select distinct CONVERT(nvarchar(20),c.clientcode_c) AS client_id, --Legacy CDT# (must be the same legacy ID for all conversion tables) CONVERT(nvarchar(50),c.lastname_vc) AS last_name, CONVERT(nvarchar(50),c.firstname_vc) AS first_name, CONVERT(nvarchar(15),isnull(c.middlename_vc,'')) AS middle_name, CONVERT(nvarchar(20),c.altclientcode_vc) AS agency_id_no, --TNKIDS Number CONVERT(nvarchar(50), CASE WHEN c.gender_c = 'M' THEN 'Male' WHEN c.gender_c = 'F' THEN 'Female' WHEN c.gender_c = 'U' THEN 'Unknown' ELSE 'Unknown' END) AS gender, CONVERT(varchar(20), CASE WHEN c.gender_c = 'M' THEN 'M' WHEN c.gender_c = 'F' THEN 'F' WHEN c.gender_c = 'U' THEN 'U' Else 'U' END ) AS gender_code, CONVERT(nvarchar(9),(SUBSTRING(c.socialsecnum_c, 1,3)+SUBSTRING(c.socialsecnum_c, 5,2)+SUBSTRING(c.socialsecnum_c, 8,4))) AS ss_number, c.birthdate_d AS date_of_birth, CONVERT(nvarchar(50), address1_vc) AS street_address_1, CONVERT(nvarchar(50), address2_vc) AS street_address_2, CONVERT(nvarchar(50), city_vc) AS City, CONVERT(nvarchar(50), state_c) AS state, CONVERT(nvarchar(2), state_c) AS state_code, CONVERT(nvarchar(9), zip_c) AS zip_code, CONVERT(nvarchar(50), CASE WHEN c.religion_c = 'A' THEN '7th Day Adventist' WHEN c.religion_c = 'AG' THEN 'Agnostic' WHEN c.religion_c = 'AT' THEN 'Atheist' WHEN c.religion_c = 'B' THEN ' Buddhist' WHEN c.religion_c = 'BA' THEN 'Baptist' WHEN c.religion_c = 'C' THEN 'Catholic' WHEN c.religion_c = 'E' THEN 'Episopalian' WHEN c.religion_c = 'EC' THEN 'Ecumencial' WHEN c.religion_c = 'H' THEN 'Hindu' WHEN c.religion_c = 'HG' THEN 'Huguenot' WHEN c.religion_c = 'J' THEN 'Jewish' WHEN c.religion_c = 'JW' THEN 'Jehovahs Witness' WHEN c.religion_c = 'L' THEN 'Lutheran' WHEN c.religion_c = 'MU' THEN 'Muslim' WHEN c.religion_c = 'ME' THEN 'Methodist' WHEN c.religion_c = 'MEN' THEN 'Mennonite' WHEN c.religion_c = 'MO' THEN 'Mormon' WHEN c.religion_c = 'M' THEN 'Moslem' WHEN c.religion_c = 'N' THEN 'None' WHEN c.religion_c = 'NO' THEN 'Nondenominational' WHEN c.religion_c = 'O' THEN 'Other' WHEN c.religion_c = 'P' THEN 'Protestant' WHEN c.religion_c = 'PC' THEN 'Pentecostal' WHEN c.religion_c = 'PS' THEN 'Presbyterian' WHEN c.religion_c = 'Q' THEN 'Quaker' WHEN c.religion_c = 'UN' THEN 'Unknown' WHEN c.religion_c = 'UT' THEN 'Unitarian' ELSE 'Unknown' END) AS religion, UPPER(CONVERT(varchar(20), CASE -- Cased out religion codes KMH 06/17/10 WHEN c.religion_c = 'A' THEN 'A' WHEN c.religion_c = 'AG' THEN 'AG' WHEN c.religion_c = 'AT' THEN 'AT' WHEN c.religion_c = 'B' THEN ' B' WHEN c.religion_c = 'BA' THEN 'BA' WHEN c.religion_c = 'C' THEN 'C' WHEN c.religion_c = 'E' THEN 'E' WHEN c.religion_c = 'EC' THEN 'E' WHEN c.religion_c = 'H' THEN 'H' WHEN c.religion_c = 'HG' THEN 'HG' WHEN c.religion_c = 'J' THEN 'J' WHEN c.religion_c = 'JW' THEN 'JW' WHEN c.religion_c = 'L' THEN 'L' WHEN c.religion_c = 'MU' THEN 'MU' WHEN c.religion_c = 'ME' THEN 'ME' WHEN c.religion_c = 'MEN' THEN 'MEN' WHEN c.religion_c = 'MO' THEN 'MO' WHEN c.religion_c = 'M' THEN 'M' WHEN c.religion_c = 'N' THEN 'N' WHEN c.religion_c = 'NO' THEN 'NO' WHEN c.religion_c = 'O' THEN 'O' WHEN c.religion_c = 'P' THEN 'P' WHEN c.religion_c = 'PC' THEN 'PC' WHEN c.religion_c = 'PS' THEN 'PS' WHEN c.religion_c = 'Q' THEN 'Q' WHEN c.religion_c = 'UN' THEN 'UN' WHEN c.religion_c = 'UT' THEN 'UT' ELSE 'UN' END)) AS religion_code, CONVERT(nvarchar(50), --Added ethnicity case statement KMH 5/26/10 CASE WHEN c.race_c = 'H' THEN 'Hispanic' ELSE 'Non Hispanic' END) AS ethnicity, CONVERT(nvarchar(20), --Added ethnicity case statement KMH 5/26/10 CASE WHEN c.race_c = 'H' THEN '01' ELSE '02' END) AS ethnicity_code, CONVERT(nvarchar(50), CASE WHEN c.race_c = 'A' THEN 'Asian' WHEN c.race_c = 'AI' THEN 'American Indian' WHEN c.race_c = 'B' THEN 'African American' WHEN c.race_c = 'BR' THEN 'Bi-racial' WHEN c.race_c = 'C' THEN 'Caucasian' WHEN c.race_c = 'H' THEN 'Hispanic' WHEN c.race_c = 'ME' THEN 'Middle Eastern' WHEN c.race_c = 'N' THEN 'Native Hawaiian/Other Pacific Islander' WHEN c.race_c = 'O' THEN 'Other' ELSE 'Other' END) AS race_1, UPPER(CONVERT(varchar(20), CASE WHEN c.race_c is NULL THEN 'U' WHEN c.race_c = 'A' THEN 'A' WHEN c.race_c = 'AI' THEN 'AI' WHEN c.race_c = 'B' THEN 'B' WHEN c.race_c = 'BR' THEN 'BR' WHEN c.race_c = 'C' THEN 'C' WHEN c.race_c = 'H' THEN 'H' WHEN c.race_c = 'ME' THEN 'ME' WHEN c.race_c = 'N' THEN 'N' WHEN c.race_c = 'O' THEN 'O' ELSE 'U' END))AS race_1_code, CONVERT(nvarchar(150), 'ar.client') AS original_table_name from ar.client c INNER JOIN cd.enrollments en ON (c.uniqueid_c = en.clientid_c) INNER JOIN cd.episode ep ON (ep.uniqueid_c = en.episodeid_c and ep.clientid_c = c.uniqueid_c) LEFT JOIN dbo.GC_clientaddress ad ON (ad.clientcode_c = c.clientcode_c) where (ep.enddate_d is NULL OR ep.enddate_d >= getdate()-729) and en.location_c in (select code from dbo.yv_LKUP_OfficeLocation where state = 'TN')--change data pull with states here (check location codes on dbo.yv_LKUP_OfficeLocation) Just in case you need this, this is the other index on this Client Information table: USE [generic_conversion] GO /****** Object: Index [Client_Demographics_PK] Script Date: 08/06/2012 13:22:17 ******/ ALTER TABLE [dbo].[Client_Information] ADD CONSTRAINT [Client_Demographics_PK] PRIMARY KEY NONCLUSTERED ( [client_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO Here is the table definition of CLient_Information: USE [generic_conversion] GO /****** Object: Table [dbo].[Client_Information] Script Date: 08/06/2012 13:23:40 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Client_Information]( [client_information_id] [uniqueidentifier] NULL, [client_id] [nvarchar](20) NOT NULL, [last_name] [nvarchar](50) NOT NULL, [first_name] [nvarchar](50) NOT NULL, [middle_name] [nvarchar](15) NULL, [agency_id_no] [nvarchar](20) NULL, [gender] [nvarchar](50) NOT NULL, [gender_code] [varchar](20) NULL, [ss_number] [nvarchar](9) NULL, [driver_license_number] [nvarchar](15) NULL, [date_of_birth] [datetime] NULL, [city_of_birth] [nvarchar](50) NULL, [state_of_birth] [nvarchar](50) NULL, [state_of_birth_code] [nvarchar](2) NULL, [country_of_birth] [nvarchar](50) NULL, [street_address_1] [nvarchar](50) NULL, [street_address_2] [nvarchar](50) NULL, [City] [nvarchar](50) NULL, [state] [nvarchar](50) NULL, [state_code] [nvarchar](2) NULL, [zip_code] [nvarchar](9) NULL, [religion] [nvarchar](50) NULL, [religion_code] [varchar](20) NULL, [citizenship] [nvarchar](50) NULL, [citizenship_code] [varchar](20) NULL, [marital_status] [nvarchar](50) NULL, [marital_status_code] [varchar](20) NULL, [ethnicity] [nvarchar](50) NULL, [ethnicity_code] [varchar](20) NULL, [primary_language] [nvarchar](50) NULL, [primary_language_code] [varchar](20) NULL, [secondary_language] [nvarchar](50) NULL, [secondary_language_code] [varchar](20) NULL, [day_phone] [nvarchar](15) NULL, [evening_phone] [nvarchar](15) NULL, [mobile_phone] [nvarchar](15) NULL, [pager] [nvarchar](15) NULL, [email_address] [nvarchar](50) NULL, [race_1] [nvarchar](50) NULL, [race_1_code] [varchar](20) NULL, [race_1_other_description] [nvarchar](50) NULL, [race_2] [nvarchar](50) NULL, [race_2_code] [varchar](20) NULL, [race_2_other_description] [nvarchar](50) NULL, [curr_employment_name] [nvarchar](50) NULL, [curr_employment_business] [nvarchar](50) NULL, [curr_employment_position] [nvarchar](50) NULL, [curr_employment_status] [nvarchar](50) NULL, [curr_employment_status_code] [varchar](20) NULL, [curr_employment_phone] [nvarchar](15) NULL, [curr_employment_start_date] [datetime] NULL, [school_attended] [nvarchar](50) NULL, [school_attended_code] [nvarchar](20) NULL, [education_degree] [nvarchar](50) NULL, [education_degree_code] [varchar](20) NULL, [education_highest_grade] [nvarchar](50) NULL, [education_highest_grade_code] [varchar](20) NULL, [urn_no] [nvarchar](9) NULL, [county] [nvarchar](50) NULL, [county_code] [nvarchar](50) NULL, [Salutation] [nvarchar](15) NULL, [salutation_code] [varchar](20) NULL, [fax_number] [nvarchar](15) NULL, [name_suffix] [varchar](50) NULL, [other_id_no] [varchar](30) NULL, [original_table_name] [nvarchar](150) NULL, CONSTRAINT [Client_Demographics_PK] PRIMARY KEY NONCLUSTERED ( [client_id] 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 SET ANSI_PADDING OFF GO Also, for reference. PK defintion: USE [generic_conversion] GO /****** Object: Index [Client_Demographics_PK] Script Date: 08/06/2012 13:24:04 ******/ ALTER TABLE [dbo].[Client_Information] ADD CONSTRAINT [Client_Demographics_PK] PRIMARY KEY NONCLUSTERED ( [client_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO And finally, the trigger: USE [generic_conversion] GO /****** Object: Trigger [dbo].[tg_Client_Information] Script Date: 08/06/2012 13:24:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create trigger [dbo].[tg_Client_Information] on [dbo].[Client_Information] for insert as begin update Client_Information set client_information_id = newid() from inserted end GO Ryan D

you can use CONFIGURE ERROR OUTPUT for handling such errors in destination and redirect them to another destination for logging or ignore them. below you can read the solution: http://www.rad.pasfu.com/index.php?/archives/23-How-to-handle-Failed-Rows-in-a-Data-Flow.htmlhttp://www.rad.pasfu.com