2
votes

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)
1
The way you have this set up I'm not sure it's possible to trap the error with SSIS (but I'm interested to know if this is incorrect). Presumably the insert is failing because the transformed Customer_Number violates the PK in Logins, but note you are only checking an untransformed version of CUSTOMER_NUMBER for 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 a WHERE clause and into a LEFT JOIN. Make sense? - andy holaday
I get what you are saying. I actually posted the wrong code, but what I'm trying to do is similar, just inserting to the logins table from a different source. I changed to a left join, but of course I still get hit with the PK violation. I was wondering if within SSIS there was a way to accomplish what I'm trying to do, simply skipping the record with a duplicate PK value. - Kerberos42
Updated code: INSERT INTO LOGINS SELECT DISTINCT REPLACE(CDA_PHONE, '-', ''), LEFT(CDA_NAME, 4) + RIGHT(CDA_PHONE, 4), CDA_NAME, CDA_CUSTOMER_NUMBER, CDA_EMAIL, 1, CDA_ALLOW_WEB_ORDERING, 0, 0, 5, CDA_LOCATION_CODE FROM CUSTOMER_DELIVERY_ADDRESS LEFT JOIN LOGINS ON REPLACE(CDA_PHONE, '-', '') = USERID WHERE CDA_PHONE <> '000-000-0000' - Kerberos42
The first two columns in the select are the Username (phone number with the -'s removed, and the password (created from the name and phone number) The where clause removes records with no phone number (defaulted to 000-000-0000 in the XML source data) So I just need to deal with the duplicate phone numbers when creating the logins IDs. - Kerberos42
I should add that this Execute SQL Task is a component is a much larger SSIS package that runs as a scheduled job in SQL Agent, so it needs to be automated, and not fail on dups. - Kerberos42

1 Answers

0
votes

if you want to submit a query like yours, you cant. You cant submit a batch of 10 rows and commit 8 and fail 2,for example.

I dont think this solution is good, I think you should somehow test if the login exist before trying to insert the data, but if you want, you can configure the maxNUmberOfErrors of the component to a very big number and try to insert the logins one by one on a loop component. The component fail wont cause the package failure until the number of failures reach the maxNUmberOfErrors.