sql Trigger

Question

sql Trigger

Tara-1044200

SSCoach

Points: 15951
More actions
July 16, 2009 at 2:52 pm

#155974

I import a text file in to a table where i get some bad dates because of which my ssis pkgs fail to run and so i want to adda trigger to my destination table so that after file is imported into the table it has to fire a trigger where it replaces bad date with NULL values.
something like where isdate(fromdate)=0 replace with NULL.
Could someone let me know how do i do that or is there any other idea to handle this issue.
thanks

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Create a calculated column in the Data Flow task in your SSIS package, build the conversion in there, and use that column instead of the original data.

Does that help?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

when you say calculated column is it a derived column in ssis where i need to add conversion.

I do have a derived column in between the flat file and destination tables what changes i need to make there to update datefiled with null if it is a worng date.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 3

Any advise on creating a trigger or adding a component in the pkg?

GSquared SSC Guru Points: 260824 More actions · Answer 4

Calculated column = Derived column. I'm too used to what they're called in the database, gave you the wrong name. Sorry about that.

You should be able to add a derived column in the data flow that will do the conversion for you, and you should be able to make it null if it's not in the range you want.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Tara-1044200 SSCoach Points: 15951 More actions · Answer 5

I do have a derived column but i can not update with null at that point may be i have to add script component to check

IF isdate( mydate)=False then mydate=NULL. I dont think this is the write code in vb script could some please correct it.

But if there is a way to pass null for invalid dates in derived column expressions that would still be gr8

thanks

Tara-1044200 SSCoach Points: 15951 More actions · Answer 6

I tried trigger on a table so that it inserts null into invalid date fields but unfortunately the trigger is not working after insert. I have only 1 way now..to change something on ssis part.