The easiest question in the world?

Question

The easiest question in the world?

n00bie

Valued Member

Points: 52
More actions
June 3, 2009 at 9:38 am

#132403

Hi Guys,
I'm a newbie to SSIS, but seem to have been landed with the majority of DB tasks, so looks like I'll be making this place my new home :-).
The problem I'm having must be something you guys/guyettes do every day...
To simplify my problem... I've a CSV file I want to import with the following:-
[font="Courier New"]Joe Bloggs;Z0674 alksdjflkasjdflajsdflkasdj
John Smith;Z4836 laksdfkasjdflkasjdflkasjdflkajsdflajsf
Jarvis Cokcer;Z6726 jkjhkjgfjhgfgf[/font]
Where the first value is name, and the second a 5-character code associated with the person (their clock-card number to be specific). However, the 2nd column has random junk at the end.
My destination table is pretty simple:
[font="Courier New"]FullName - nvarchar[50]
ClockNum - nvarchar[5][/font]
So... I run through the "SSIS Import and Export Wizard"...
But I can't figure out how to tell it to do a [font="Courier New"]LEFT(col, 5)[/font] type of command so only the first 5 characters get imported. I keep getting variations on:-
[Data Conversion 1 [53]] Error: Data conversion failed while converting column "colClock" (22) to column "colClock" (73). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
I've gone through a few tutorials now, but they're all slightly different.
Can anybody help?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

I suggest that you import your data as two columns: Name, TheRest and add a derived column transformation, ClockNum, which is defined as Left(TheRest, 5).

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

n00bie Valued Member Points: 52 More actions · Answer 2

Hi, thanks for the tip, it helped get things working :).

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

Another approach you can use is to configure a flat file reader where:

1. First column separator is ;

2. Second column separator is [space]

3. Third column separator is [CR][LF]

And then import the data from the first 2 columns.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/