SSIS Problem while exporting number to Excel
Hi All, In SSIS am exporting data from a SQL Server table whose datatype is numeric (7,7). the exact data in source is 0.0100000 But when I export it to excel it becomes .0100000 But I want it to be exact as it's in my source. And In SSIS package i am converting numeric to DT_WSTR before loading. Please suggest a workaround to this prob. Thanks Subrat
May 16th, 2012 10:05am

So your problem is that you want the leading 0 in your destination string? You could just do an expression on your converted DT_WSTR and prepend a 0 to the string when the first character is a decimal pointChuck
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 10:10am

Something like this: SUBSTRING(@[User::StringVar] ,1, 1) == "." ? "0" + @[User::StringVar] : @[User::StringVar]Chuck
May 16th, 2012 10:13am

Thanks Chuck for the response For the data having values as 2.0100000 is coming proper. The only prob is when it has 0.0100000 Chuck this data is dynamic its not just a string. as Excel cant read numeric so am converting to Unicode String. So i cant append a 0 before the data. Thanks
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 10:17am

Thanks Chuck for the response For the data having values as 2.0100000 is coming proper. The only prob is when it has 0.0100000 Chuck this data is dynamic its not just a string. as Excel cant read numeric so am converting to Unicode String. So i cant append a 0 before the data. Thanks The expression I listed will only prepend a 0 if the first character is a decimal point. It should fix your problem. Just replace @[User::StringVar] with the column name in your data flowChuck
May 16th, 2012 10:31am

If I remember right, this issue gets resolved once you instruct Excel to be in import mode by modifying its connection string to include IMEX=1 This is what I was able to Bing for your reference: http://swap.wordpress.com/2007/09/18/solution-for-excel-data-uploading-problem-using-ssis/Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 10:52am

Hi Chuck My doubt is how can i use an expression in Data Conversion Transformation. I tried converting the source to numeric giving scale and precision and I observed the data is absolutely fine in GridViewer just before the excel destination. But while it gets loaded the initial 0 before decimal is missing. I even tried formatting the column in excel before loading...didn't work out.. Thanks
May 16th, 2012 1:33pm

You use the expression in a Derived column block after your data conversion - use a replace column type derived column with the expression I provided Chuck
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 1:36pm

Thanks a ton Chuck You saved me... :) Seems to be working like charm... Appreciate your help... Thanks
May 16th, 2012 2:09pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics