Solved

ssis does not convert decimal positions correctly

Posted on 2011-03-04
9
888 Views
Last Modified: 2012-05-11
I am converting an excel to sql using ssis import/export wizard. The problem I am having is, one of my fields has 5 decimal positions. SSIS converts it to 2. What do I need to do to correct that issue.
0
Comment
Question by:qbjgqbjg
[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
  • 5
  • 2
9 Comments
 
LVL 5

Expert Comment

by:jijeesh
ID: 35040704
You can do it in "Advanced Editor"

Right Click on the "Excel Source" in Data flow task
Go "Show Advanced Editor"
Goto Input Output Properties tab and update your column's datatype as required.
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 500 total points
ID: 35041521
When you define a numeric variable in SQL, there are 2 parameters: precision and scale

So if the datatype is numeric(18, 5), it means you can have 18 digits total and 5 of them can be to the right of the decimal place.

So you'll need to define your data type as a numeric(??, 5), depending on how large your #s get.
0
 

Author Comment

by:qbjgqbjg
ID: 35043732
So, if I change the data type to decimal(18,5) it should work. Can I do that under the wizard? Would I still need to change it in the Excel?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:qbjgqbjg
ID: 35043991
Thanks for removing Crystal as a zone. I did not add it. It must have remained from my previous question.
0
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 500 total points
ID: 35044964
Yes, when you're on the "Select Source Tables and Views" portion, click "Edit Mappings"

From there, you should see the Precision/Scale columns and can change your definitions accordingly.
0
 

Author Comment

by:qbjgqbjg
ID: 35046155
Thanks, I will not have access to try it until Monday. I will try it then.
0
 

Author Comment

by:qbjgqbjg
ID: 35058415
It worked. Thanks.
0
 

Author Closing Comment

by:qbjgqbjg
ID: 35058427
Thanks so much. I appreciate your help.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

691 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