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

  • 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,

  • 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

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

  • 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

  • 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.

  • 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

  • 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,

  • 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.

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

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