Solved

SSIS data type mismatch in criteria expression - need help

Posted on 2013-11-26
4
1,070 Views
Last Modified: 2016-02-11
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
Comment
Question by:BKennedy2008
  • 2
4 Comments
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39678315
is the column F2 formatted as a number if it isnt it will be read as text even though it contains a number
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39678336
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
 

Author Closing Comment

by:BKennedy2008
ID: 39678441
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39678468
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question