I have a SSIS package that imports a bunch of XML files into SQL tables. Once the XML import is done, I run an Execute SQL task to update some records, and import others that don't already exist. One of the tasks creates a user login based on their phone number. sometimes the source data has errors, where no number has been input, or a duplicate number is input. I have no control over the data entry, but the userlogon table of course needs unique values.
How can I configure the Exec SQL Task to simply ignore the PK Violation error and continue with the next record?
Here's the SQL code:
insert into Logins
select REPLACE(LTRIM(REPLACE(Customer_Number, '0', ' ')), ' ', '0'), RIGHT(customer_number,4) + EFFDAT, Company_Name, Customer_Number, Email_Address, 0, CU_Allow_Web_Ordering, 0, 0, 1 from CUSTOMER_MASTER_FILE where CUSTOMER_NUMBER not in
(select CustomerNumber from Logins)
Customer_Numberviolates the PK inLogins, but note you are only checking an untransformed version ofCUSTOMER_NUMBERfor existence--and not checking for nulls at all, as your problem suggests you should. Moreover, you would be better off moving that check out of aWHEREclause and into aLEFT JOIN. Make sense? - andy holaday