August 5, 2010 at 4:20 am
Morning all
Just thought I'd see if anyone had any opinions on this problem:
On import of an xls file with no data in it the source component is failing due to data type mismatching. I'm currently using a sql task with an openrowset on the file to validate it first and skip the dataflow if the rowcount = 0. This works a treat but I'm sure there must be a simpler way of doing this, has anyone got any alternative ways to get around this?
August 5, 2010 at 5:08 am
Laurence an empty cell in Excel corresponds to a NULL field in SSIS
So if you can manipulate the NULL field to some meaningful entity you have a work around....
Raunak J
August 5, 2010 at 5:19 am
Hmm, so it seems some of the data types in the source are not validating against null values which seems a little strange.
These are the data types in the output columns
Int (DT_UI4)
string (DT_STR)
Unicode String (DT_WSTR)
Currency (DT_CY)
Hardly rocket science, I wouldn't imagine any of them should have problems with null values???
August 5, 2010 at 5:26 am
Anyways you may use a Script Task to validate your incoming file...
But a word of caution:
You are inviting a lot of .NET I/O code to be composed.
Happy coding
Raunak J
August 5, 2010 at 5:31 am
yes, this was my first thought due to being able to do much more with the validation, but the vast majority of my team are scared of .net so need to keep it in sql till i convert them 😉
Oh well, looks like I'll stick to the current solution then. Just wanted to make sure there weren't any connection string modifications (or similar) that I hadn't thought of.
Thanks Raunak
August 5, 2010 at 5:33 am
Yeppie the haunting of coding continues....:-D:-D:-D
happy to help you
Raunak J
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply