skip records that have null values but required in destination table

Question

skip records that have null values but required in destination table

sqlfriend

SSC Guru

Points: 52781
More actions
May 19, 2014 at 1:12 pm

#308186

I have a SSIS package to import data from other source to a SQL server database.
The job failed last night is because the source system has a missing last name of the student.
Since in the destination table last name is required, it failed the job.
Is there a way to avoid this failure by ignoring or skipping the records when doing the data load for those have null values for required columns in destination.,
what is the best way to implement it?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 1

Hi Yes there is

Configure a second destination to a file or a table and set errored rows to redirect to this destination rather than fail the package

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

aaron.reese SSChampion Points: 13498 More actions · Answer 2

Andy is correct but the explanation is a bit light on content 😉

The problem occurs with the database constraint and is not an error in the SSIS dataflow, therefore you cannot trap the record at insert. Instead you need to carry out a sanity check before the database destination.

There are a number of different solutions for this depending on how many fields you need to check and what else you are doing with the data, but the simplest is to use a conditional split and validate that the field is not null (and is not too long, and that it contains only letters, and that it is capitalised etc, etc) and if it fails these checks then divert it to a different processing stream. I would do these checks as early on as possible in the dataflow so that you do not waste CPU cycles deriving values and looking up matches for records which you can't insert into the target system.

Hope that helps.

Aaron

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

but the explanation is a bit light on content [Wink]

Indeed I was 😉

This was intentional (just wanted to point in the right direction) as there is a wealth of information out there on redirecting error rows within SSIS and blogs that have been written

with diagrams that show the process

Didn't feel like it was worth re-inventing the wheel and if the OP still had questions would have done an <Insert Search Engine Here> myself for a link 😉

Still nice explanation Arron 🙂

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

sqlfriend SSC Guru Points: 52781 More actions · Answer 4

Thanks Arron, I do need the detailed explanation that helps me to clarify.

I knew earlier there was an option for directing errors not to fail the package, but not clear in my situation how to use it.

The approach Arron mentioned using conditional split certainly is one of the options I can use.

Else, I found out the ssis package first import the data into a table that has raw data, meaning no constraints defined, then import from raw table to the real table that has constrains using a stored procedure, so my other option is to deal with the nulls in the stored procedure to check if each not null field and only if they are null, skip the record.

Thanks

MMartin1 One Orange Chip Points: 27799 More actions · Answer 5

Else, I found out the ssis package first import the data into a table that has raw data, meaning no constraints defined, then import from raw table to the real table that has constrains using a stored procedure, so my other option is to deal with the nulls in the stored procedure to check if each not null field and only if they are null, skip the record.

You shouldn't have to use t-sql to clean your data as it is slower than the transforms that come in SSIS. The conditional split should work fine for you , it needs to use a new/or different connection manager so it knows where to stash the rows with that Null.

As an added bonus,

In your OLE DB source , go to the Error Output tab. There you can select a column and choose to redirect that row also based on if an unforseen error or truncation occurs. Play around with a test package, it should not be too difficult. Keep in mind any agent/context that runs the ssis package will need permissions to the locations indicated by your connections.

----------------------------------------------------