Inconsistent datatypes of excel source files

Question

Inconsistent datatypes of excel source files

PSB

SSChampion

Points: 13195
More actions
September 8, 2011 at 9:23 pm

#256822

Hi,
I have a SSIS package that processes excel files dynamically. I have two questions.
1) The files are inconsistent . The datatypes of a Field called Vehicle is sometimes DT_R8 and sometimes it is DT_WSTR . Thats why the file with DT_R8 fails to load . Is there anyway I can fix in my package so that all files gets loaded or this is something that needs to be controlled in the source ?
2) The files names are not constant . So if I just want to get the file name from the variable [User::FileName] without the path,how do I do that ? Right now I am using the RIGHT function. But it doesnot return the filename correctly always as the names are not constant.
Thanks,
PSB

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

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

kramaswamy SSCoach Points: 18181 More actions · Answer 1

For 1), I've encountered similar problems when dealing with Excel files. Your best bet is to load the values into SSIS as DT_WSTR, give it a length of like, 12 let's say, just to ensure you get all the possible values.

Then, do a Data Conversion, and convert the value to DT_I8. Put an error output to redirect row or ignore failure, so that the values which can't be cast to DT_I8 will not cause your flow to crash.

For 2, you should be able to just find the last slash.

If your filenames are in the standard UNC path format, for example,

//server/share/filename.extension

Then what you'll do is something like:

REVERSE(SUBSTRING(REVERSE([Variable]), FINDSTRING("/", REVERSE([Variable]), 1), LEN([Variable])))

Might have to add some +1's or -1's in there, but you should be able to get it to work without too much trouble.

PSB SSChampion Points: 13195 More actions · Answer 2

I get the below error if I use the expression that you suggested.

The start index 0 is not valid for function "SUBSTRING". The start index value must be an integer greater than 0. Start index is one-based, not zero-based.

Evaluating function "SUBSTRING" failed with error code 0xC004708C.

Evaluating function "REVERSE" failed with error code 0xC00470C5.

kramaswamy SSCoach Points: 18181 More actions · Answer 3

Paste an example of your file names and I'll see what I can do

PSB SSChampion Points: 13195 More actions · Answer 4

It might be just anything . Currently in QA we have files like ME_01_2011.07.29AMTRAK.xls

LTL_08-16-11_IVS_Hydro.xls

BurkeOil080111-080711.xls etc

The path of the source files is P:\Import\FolderName_QA\

This path might/will change later .

kramaswamy SSCoach Points: 18181 More actions · Answer 5

Ah okay - so you're not using UNC path format.

Okay, this should work then:

REVERSE(SUBSTRING(REVERSE([Variable]), 1, FINDSTRING("\", REVERSE([Variable]), 1)))

PSB SSChampion Points: 13195 More actions · Answer 6

I get the error below :

TITLE: Expression Builder

------------------------------

Expression cannot be evaluated.

Attempt to parse the expression "REVERSE(SUBSTRING(REVERSE( @[User::FileName]),1, FINDSTRING("\", REVERSE(@[User::FileName]), 1)))

" failed. The token """ at line number "1", character number "61" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

kramaswamy SSCoach Points: 18181 More actions · Answer 7

This is what I meant by testing and playing around with it yourself 😛

Anyway, after setting up a test environment on my end and messing around with it, this should work:

REVERSE(SUBSTRING(REVERSE(InputVar),1,FINDSTRING(REVERSE(InputVar),"\\",1) - 1))