Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Ignore/Skip data coming from falt file and loading into SQL table Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2012 3:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:59 AM
Points: 45, 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,
Post #1398069
Posted Tuesday, December 18, 2012 3:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 6,158, Visits: 7,223
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
Posted Tuesday, December 18, 2012 3:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:59 AM
Points: 45, Visits: 342
I want to know from T-SQL(store proc) in a staging table and also SSIS.
Post #1398079
Posted Tuesday, December 18, 2012 4:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 6,158, Visits: 7,223
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
Post #1398092
Posted Tuesday, December 18, 2012 9:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:59 AM
Points: 45, 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.
Post #1398146
Posted Wednesday, December 19, 2012 3:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 6,158, Visits: 7,223
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
Post #1398693
Posted Wednesday, December 19, 2012 5:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:59 AM
Points: 45, 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,
Post #1398714
Posted Thursday, December 27, 2012 3:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 5:32 AM
Points: 55, Visits: 158
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 »

Add to briefcase

Permissions Expand / Collapse