?
Solved

Problem with Lookup Data Flow Component

Posted on 2011-09-13
22
Medium Priority
?
3,256 Views
Last Modified: 2013-11-10
I'm trying to map available input column (IDno) to available lookup column (IDno) and I get an error  "cannot map the lookup column "IDno" because the column is set to a floating point data type
0
Comment
Question by:ktjamms2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 9
  • 2
22 Comments
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36531551
Floating is not supported, see that post

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/8dcc2705-51a6-49de-aa5c-d538087059f1

so as a workaround, convert it to a different data type for example sting, and do a lookup against a select statement and convert your column too to the same data type.

0
 

Author Comment

by:ktjamms2
ID: 36531702
I'm not sure I'm following this
<<<<so as a workaround, convert it to a different data type for example sting, and do a lookup against a select statement and convert your column too to the same data type.>>>

Should I change the data type properties in the Advanced Editor for OLE DB Source for the External Columns, Output Columns, OLE DB Source Error Output, or all of the above?
0
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 2000 total points
ID: 36531743
there's a data conversion task in SSIS to convert your desired column.

Also in the lookup, don't select a table, select from SQL query and convert the column that you'll lookup against.

 lookup
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:ktjamms2
ID: 36531772
Can you give me a sample query?
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36533222
Sure...
look at the last attached pic...what's your query?
0
 

Author Comment

by:ktjamms2
ID: 36533423
How would you then handle getting it converted back to the original data type (float) for the upsert?
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36534124
Please before map make sure the data type of both source and destination columns should be same.
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36535676
You still can use the Data Conversion Task to get it back...the problem with float that it's not an exact it's approximate ,that's why it's not supported.
0
 

Author Comment

by:ktjamms2
ID: 36536190

I'm getting a big huge error with this:

SELECT CONVERT(varchar, IDno) AS IDNOCONVERTED FROM tablename LOOKUP-ERROR-1.docx
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36536263

Adjust the 50 for your desired results, don't go too much over it :)
Select cast(IDno as varchar(50))  AS IDNOCONVERTED FROM tablename

Open in new window

0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36536266
validate the query first in SSMS and make sure it runs fine, before you use it in SSIS lookup screen
0
 

Author Comment

by:ktjamms2
ID: 36536370
It does fine in Server Management Studio, but I'm still getting the  error in SSIS. A normal select query runs fine, but when I add the convert it blows up
0
 

Author Comment

by:ktjamms2
ID: 36536390
I get the same error using cast
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36536580
Then convert it to a discrete precision value - such as a decimal, currency, or integral type.

what is that column data looks like anyway? currency or what? can you post examples?
0
 

Author Comment

by:ktjamms2
ID: 36536677
It's just a number. I tried this:
 cast(IDno as int) as CASTIDno

Getting the same error. I tried parse query and the error reads:
Error in list of function arguments: 'AS' not recognized. Unable to parse query text.
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36536708
ok, I'm not sure what to tell you, but  there's something wrong with your query.
Anyway as a work around, create a view in the SQL DB since the convert or cast works fine there, as a workaround and it should be faster, then select the VIEW in the lookup.
0
 

Author Comment

by:ktjamms2
ID: 36536744
These are dBASE tables that I'm trying to get into a SQL database
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36541058
Hey Just convert using Conversion task and make sure first trim it.
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36543118
I'm not really familiar with dBase, but as a work around, create a view in the SQL server that gets it's data from the DBase, then use it in the lookup.
0
 

Author Comment

by:ktjamms2
ID: 36543138
Hi PatelAlpesh:
I used the Data Conversion tool and made a copy of IDno with a numeric data type, but when I try to map the copy of ID no to IDno in the lookup tool I'm still getting the same float data type error. Is it because the destination table is still float? I am supposed to use the destination table for the lookup right?
0
 

Author Comment

by:ktjamms2
ID: 36546006
The source and destination data types are both float
0
 

Author Closing Comment

by:ktjamms2
ID: 36710138
Thank-you!
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

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 this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

777 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