Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » excel help...in ssis...
Add to briefcase

excel help...in ssis... Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
sathiyan00
Posted Monday, September 24, 2012 11:21 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 4:12 AM
Points: 60, Visits: 328
Hi all,
i have to move invalid records(data type mismatch) from excel into error log and have to process only valid records.
But the problem is when there is mismatch data type like :
in DT_WSTR there is value 1234
in DT_R8 there is value xxx while reading only the excel source replace it with null.

but i want to move that record into error log file ....


thanks and regards
sathiyan.R
Post #1363789
Sean Lange
Posted Tuesday, September 25, 2012 12:26 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 8,957, Visits: 8,523
sathiyan00 (9/24/2012)
Hi all,
i have to move invalid records(data type mismatch) from excel into error log and have to process only valid records.
But the problem is when there is mismatch data type like :
in DT_WSTR there is value 1234
in DT_R8 there is value xxx while reading only the excel source replace it with null.

but i want to move that record into error log file ....


thanks and regards
sathiyan.R


Can you try to explain more clearly what you are trying to do?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1364233
Evil Kraig F
Posted Tuesday, September 25, 2012 1:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:35 PM
Points: 5,722, Visits: 6,194
sathiyan00 (9/24/2012)
Hi all,
i have to move invalid records(data type mismatch) from excel into error log and have to process only valid records.
But the problem is when there is mismatch data type like :
in DT_WSTR there is value 1234
in DT_R8 there is value xxx while reading only the excel source replace it with null.

but i want to move that record into error log file ....


thanks and regards
sathiyan.R

There is a way to send errored rows to another drop location, which can then be manipulated. Go to your OLEDB delivery and click on error configuration, then choose redirect row for all options.

You'll then have to take the error rows and deliver them to a location that'll handle pretty much anything that might have ended up in the column.

Now, a few questions, because what you've listed above doesn't directly translate to that.
DT_WSTR (Unicode VARCHAR()) doesn't have a problem with 1234. What's the concern there?
DT_R8 (BIGINT if memory serves, but it's numeric) will properly error on xxx, but it won't translate to NULL for you. For something like that, you'll want to include a derived column in the datastream, replace the column, and have it detect for ISNUMERIC() ? OrigColumn : DT_R8NULL (it's in the syntax above in datatypes, I forget it offhand).

This isn't simply an 'if error transfer to drop file', you are looking to perform in stream transformations and detections if I understand this right, so it's not as simple. You have to build each direct check that isn't just a 'on bad data drop over here'.

- 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 #1364259
sathiyan00
Posted Tuesday, September 25, 2012 11:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 4:12 AM
Points: 60, Visits: 328
thanks all...
my problem is excel sheet is not reading the data of mismatch data type...
my excel data is
name phone
sathiyan 1234
rahul 12345
ssss 1344
yyyy xxx

when it reads fourth row it replace 'xxx' with null. but i want to move this record to error log using link comes from excel source...
Post #1364444
ard5karthick
Posted Wednesday, September 26, 2012 12:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 5:51 AM
Points: 38, Visits: 129
Hi Sathyan,

If i understand correctly the value 'XXX' is getting replaced by NULL in the excel sheet.

If thats the case can you give us some example for the values?

Thanks,
Karthick Mageswaran.
Post #1364449
sathiyan00
Posted Wednesday, September 26, 2012 1:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 4:12 AM
Points: 60, Visits: 328
in the last post itself i given... that values only i have...
Post #1364463
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.