SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Grass
Grass
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 342
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,
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8545 Visits: 7660
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
Grass
Grass
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 342
I want to know from T-SQL(store proc) in a staging table and also SSIS.
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8545 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Grass
Grass
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 342
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
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8545 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Grass
Grass
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 342
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
eklavu
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 173
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search