SSIS data type mismatch in criteria expression - need help

I have a SSIS with an OLE DB connection Manager pointing to an Excel File. In my OLE DB Source Editor, I have a sql statement that says : Select F1 as 'This', F2 as 'total' where F2 > 0

Column F2 is a Number, but yet when i execute the dtsx package I get
data type mismatch in criteria expression  (Microsoft Office Database Engine)

I am using office 12.0 access database engine OLEDB Provider

I tried to (CAST (F12) as int) > 0 but get null
BKennedy2008Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QuinnDexCommented:
is the column F2 formatted as a number if it isnt it will be read as text even though it contains a number
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Try this..
CAST (ISNULL(F12,0) as int)

Open in new window

and if there are any non-numeric values in this column,  you'll have to decide to either weed them out of the import..
CASE WHEN ISNUMERIC(F12) = 0 THEN 0 ELSE CAST (ISNULL(F12,0) as int) END

Open in new window

or deal with it in the Excel spreadsheet before imporing.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BKennedy2008Author Commented:
Thanks I delt with the template in Excel. The Column was formated to a number.

I did have a formula in there, and said if column A was blank to make column 2 blank. Instead I made it = 0 if column 1 is blank, and even though it fills the column with zeros, it works. So I guess I will deal with that. Thanks
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.