SQL decimal to SP number issue

Mar 5, 2009 at 9:47 AM

The situation I face is this: a SQL column is defined with a data type of decimal(5,3), and when mapping it into a sharepoint column defined as a number, I see no values in SP.  Where do I start debugging, is there an intermediate file I can examine for content, etc?  Do I need to manipulate the value / format the value in SQL prior to using the dataflow?

- The grid from the data preview looks fine when it retrieves values from the SQL Server table. 
- I've also tried mapping the SQL decimal to a character type SP column to no avail

environment:
SQL 2005
SP 2007
DTS job created in Business Intelligence Development Studio 2005 (aka visual studio)

thanks
Mar 6, 2009 at 10:00 PM
Update: I've also tried doing this with the SQL source data type of numeric(18,6) and again, no values get populated in the target sharepoint list.  Any help or guidance would be great.


thanks
Mar 6, 2009 at 10:41 PM
I got the data into sharepoint;
- created a new column using a Float data type (because I noticed in sharepoint, it seems to be storing the 'numeric' datatype internally as dt_r8)
- converted my SQL columns to Float i.e. change the datatype directly or copy the column, such as UPDATE tablename set column2 = cast(column1 as float)
- remapped the SSIS / DTS package, then bang, bang.

pritish.
Coordinator
Mar 11, 2009 at 5:14 AM
I'm glad you got it working. That float is a bit annoying, but it is the way Sharepoint seems to do every numeric value.  I'm not quite sure why the decimal didn't convert and transfer into the float, that seems as though it should have worked.  I'll see if I can repro it when I get a chance and see what can be done.
Nov 19, 2009 at 12:31 AM
Edited Nov 19, 2009 at 12:32 AM

As a follow up on this, I came across another reason to use this SSIS SharePoint adapter, with the variation on the SharePoint destination datatype. 

 

Need: mapping from a SQL numeric(13,2) -> SharePoint currency

In SharePoint, I see currency's underlying datatype once again is DT_R8 - revealed inside the SSIS advanced properties of the SharePoint List Source, External Columns > DataType section.  It'd been a while since touching this part of the solution, so I had to retrace my original solution posted above.  As it turns out:

 

Solution: in SQL prior to retrieving the numeric column using the OLE DB source, convert the column into a float -- cast(column1 as float)

Afterwards, in SSIS make sure to remove the outbound OLE-DB source mapping & re-add the mapping (so it pulls the correct float/DT_R8 datatype) or edit the advanced properties on the green data flow coming from the OLE-DB source, then edit the datatype of the specific Output Column.

 

I'll post the complete SSIS walkthrough with glorious pictures on my blog; http://pritishwrites.blogspot.com

& thanks for the community contribution Kevin.