Ignore/Skip data coming from falt file and loading into SQL table

Question

Post reply

Ignore/Skip data coming from falt file and loading into SQL table

Grass

Ten Centuries

Points: 1149
More actions
December 18, 2012 at 3:21 pm

#267633

Hi,
I have issuse to figure out how to skip a data from a column in a flatfile to SQL table. If a data looks like this in a column:
NOT SPECIFIED 50B786EF-CBD4-4E29-A0A4-3DAB32F3F334
How to igone or skip this going to SQL table.
Please Help.
Thanks,

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

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

What are you using for the data load? Bulk Import? SSIS? SCD T-SQL script off a staging table?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Grass Ten Centuries Points: 1149 More actions · Answer 2

I want to know from T-SQL(store proc) in a staging table and also SSIS.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Grass (12/18/2012)
I want to know from T-SQL(store proc) in a staging table and also SSIS.

In T-SQL you'd simply use a CASE satement in your INSERT/Update statements, something like

CASE WHEN LEFT( column, 11) = 'Not Defined' THEN NULL ELSE column END

In SSIS you'd use a derived column and null the value in the column when it met your criteria.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Grass Ten Centuries Points: 1149 More actions · Answer 4

Craig,

Thanks for the info. I want to one more time explain the problem just to make sure that the statement u mentioned is write. So I have a source flat file which is in .csv format and it dumping the in data to a SQL table destination.

From the .csv file there are some columns and in the first column there is some data which is pulling AB12132123 and some data are coming as

NOT SPECIFIED 50B786EF-CBD4-4E29-A0A4-3DAB32F3F334.

the second value is not supposed to be coming in and should be ignore/skip from the source file. So the statement u mentioned is right? and if it is Do I have to write an update statement on a destination table?

Also, for SSIS in the derived column how should I write in the Expression.

The Source and destination Column Name is ROW_ID.

Please Reply

Thanks.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

Grass (12/18/2012)
the second value is not supposed to be coming in and should be ignore/skip from the source file. So the statement u mentioned is right? and if it is Do I have to write an update statement on a destination table?

You want to skip the row, or do you want to null the data out? Also, if you could, please be explicit about exactly how you're transferring the data now.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Grass Ten Centuries Points: 1149 More actions · Answer 6

I want to skip or ignore the rows that are coming from the source file. I have created SSIS package and I was pulling the data from a .csv file and dumping into SQL table.Hope I answer your question.

Thanks,

Eklavu Right there with Babe Points: 790 More actions · Answer 7

You can use script task to filter the data.

Pull the data in a flat file, filter the row and insert the data in your sql server database.