Solved

SSIS data type mismatch in criteria expression - need help

Posted on 2013-11-26
4
1,012 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now