Solved

SSIS data type mismatch in criteria expression - need help

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

932 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

18 Experts available now in Live!

Get 1:1 Help Now