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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Ignore/Skip data coming from falt file and...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Grass

Posted Tuesday, December 18, 2012 3:21 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:37 PM
Points: 42, Visits: 325

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,

Post #1398069

Evil Kraig F

Posted Tuesday, December 18, 2012 3:24 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 5,688, Visits: 6,142

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1398073

Grass

Posted Tuesday, December 18, 2012 3:41 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:37 PM
Points: 42, Visits: 325

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

Post #1398079

Evil Kraig F

Posted Tuesday, December 18, 2012 4:18 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 5,688, Visits: 6,142

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.

Post #1398092

Grass

Posted Tuesday, December 18, 2012 9:36 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:37 PM
Points: 42, Visits: 325

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.

Post #1398146

Evil Kraig F

Posted Wednesday, December 19, 2012 3:21 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 5,688, Visits: 6,142

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.

Post #1398693

Grass

Posted Wednesday, December 19, 2012 5:21 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:37 PM
Points: 42, Visits: 325

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,

Post #1398714

eklavu

Posted Thursday, December 27, 2012 3:13 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:38 AM
Points: 53, Visits: 150

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.

Post #1400535

« Prev Topic | Next Topic »

Permissions