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: 3607
  • Last Modified:

Problem with Lookup Data Flow Component

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
ktjamms2
Asked:
ktjamms2
  • 11
  • 9
  • 2
1 Solution
 
Jason Yousef, MSSr. BI DeveloperCommented:
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
 
ktjamms2Author Commented:
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
 
Jason Yousef, MSSr. BI DeveloperCommented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
ktjamms2Author Commented:
Can you give me a sample query?
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
Sure...
look at the last attached pic...what's your query?
0
 
ktjamms2Author Commented:
How would you then handle getting it converted back to the original data type (float) for the upsert?
0
 
Alpesh PatelAssistant ConsultantCommented:
Please before map make sure the data type of both source and destination columns should be same.
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
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
 
ktjamms2Author Commented:

I'm getting a big huge error with this:

SELECT CONVERT(varchar, IDno) AS IDNOCONVERTED FROM tablename LOOKUP-ERROR-1.docx
0
 
Jason Yousef, MSSr. BI DeveloperCommented:

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
 
Jason Yousef, MSSr. BI DeveloperCommented:
validate the query first in SSMS and make sure it runs fine, before you use it in SSIS lookup screen
0
 
ktjamms2Author Commented:
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
 
ktjamms2Author Commented:
I get the same error using cast
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
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
 
ktjamms2Author Commented:
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
 
Jason Yousef, MSSr. BI DeveloperCommented:
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
 
ktjamms2Author Commented:
These are dBASE tables that I'm trying to get into a SQL database
0
 
Alpesh PatelAssistant ConsultantCommented:
Hey Just convert using Conversion task and make sure first trim it.
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
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
 
ktjamms2Author Commented:
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
 
ktjamms2Author Commented:
The source and destination data types are both float
0
 
ktjamms2Author Commented:
Thank-you!
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

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