Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1221
  • Last Modified:

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
0
BKennedy2008
Asked:
BKennedy2008
  • 2
1 Solution
 
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
 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now