<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Data Warehousing
»
Integration Services
»
Data type conversion error Money-> text in...
18 posts, Page 1 of 2
1
2
»»
Data type conversion error Money-> text in excel destination
Rate Topic
Display Mode
Topic Options
Author
Message
MrReddy
MrReddy
Posted Wednesday, August 15, 2012 3:13 PM
Valued Member
Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 57,
Visits: 491
Hello All,
My SQL data type is money, when I load the data into excel it takes the value as text but I want that data to be decimal or money to use for sum calculations in Pivots.
suggestions please,.,.,
Thanks,
Ram
Post #1345564
Mordred
Mordred
Posted Wednesday, August 15, 2012 3:52 PM
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, June 04, 2013 2:25 PM
Points: 96,
Visits: 490
Have you used the Data Conversion in your Data Flow Task? Perhaps
this link
will be of some help for you.
Regards:
Mordred
Keep on Coding in the Free World
Post #1345592
MrReddy
MrReddy
Posted Wednesday, August 15, 2012 3:56 PM
Valued Member
Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 57,
Visits: 491
I did all the data conversions but the target excel file is taking all the values as text only, even if I change the data types in excel show advanced editor, it is not storing the values.
Post #1345593
Mordred
Mordred
Posted Wednesday, August 15, 2012 4:29 PM
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, June 04, 2013 2:25 PM
Points: 96,
Visits: 490
K, I'm currently trying to recreate the scenario but am having un-related issues at run time with my laptop. I'll get back to you in a bit or someone else will.
Regards:
Mordred
Keep on Coding in the Free World
Post #1345600
Mordred
Mordred
Posted Wednesday, August 15, 2012 4:45 PM
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, June 04, 2013 2:25 PM
Points: 96,
Visits: 490
Alright I see what you mean.
Questions:
Is the Excel destination pre-existing or are you generating a new Excel file each time this particular data flow task is run?
If the file is pre-existing then you should take the column in question that is to hold $$ data and format those cells appropriately.
If the file is being generated at dft time then you may want to consider connecting to your personal Excel book. That particular book should be in a folder similar to the following path:
C:\Documents and Settings\Your Name\Application Data\Microsoft\Excel\XLSTART
Open that book, alter the column of cells where you know the $$ format should be, save it and close it. From there, every time an Excel workbook is opened it will open in that format. You can customize this Workbook however you see fit.
I don't know if this is the best solution but it's all I have off the top of my head.
Regards:
Mordred
Keep on Coding in the Free World
Post #1345604
MrReddy
MrReddy
Posted Wednesday, August 15, 2012 4:45 PM
Valued Member
Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 57,
Visits: 491
Thank you for your reply Mordred.
Post #1345605
Mordred
Mordred
Posted Wednesday, August 15, 2012 4:50 PM
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, June 04, 2013 2:25 PM
Points: 96,
Visits: 490
I just did as I suggested with my own starting Excel file and it didn't seem to have the proper effects. I'm going to keep trying though.
Regards:
Mordred
Keep on Coding in the Free World
Post #1345606
Mordred
Mordred
Posted Wednesday, August 15, 2012 6:50 PM
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, June 04, 2013 2:25 PM
Points: 96,
Visits: 490
Alright so I formatted the columns that I am affecting with the data flow task and now it loads properly. Here's what I have done:
1. Created a simple table called tblMoneys
1.1 Created two fields in the table called ID & Money
2. Created a personally formatted Excel file with headers ID & Money
2.1 Formatted ID column as a number with no decimal spots, data type: double precision float [DT_R8]
2.2 Formatted Money column as currency with 2 decimal spots, data type: currency [DT_CY]
2.3 Saved and closed the workbook
SSIS:
1. Source - OLB DB
2. Added a Data Conversion (although I don't think it was necessary but still used it non-the-less.
3. Destination: Excel Connection Manager
4. Ran the package
No issues with the loading to Excel, all formats were good.
Regards:
Mordred
Keep on Coding in the Free World
Post #1345624
MrReddy
MrReddy
Posted Thursday, August 16, 2012 9:17 AM
Valued Member
Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 57,
Visits: 491
2.1 Formatted ID column as a number with no decimal spots, data type: double precision float [DT_R8]
2.2 Formatted Money column as currency with 2 decimal spots, data type: currency [DT_CY]
Hello Mordered, thank you for your reply.
How did you set that currenct[DT-CY] data type??
Post #1346048
Mordred
Mordred
Posted Thursday, August 16, 2012 9:25 AM
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, June 04, 2013 2:25 PM
Points: 96,
Visits: 490
For the layout I created in the Excel file, the currencies were placed in column B. I highlighted column B, right clicked, and then selected "Format Cells". I then chose Currency. When I made the destination connection to the Excel file, I checked the mappings and the format was as I stated.
Which version of Excel are you using? The test I performed was with Excel 2010.
Regards:
Mordred
Keep on Coding in the Free World
Post #1346056
« Prev Topic
|
Next Topic »
18 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
