asked
SSIS excel destination datatype issue
Hi, We have a requirement of exporting data from SQL Server database table to Excel. Here we wrote a SSIS package to do this. We have some currency fields like amount, premium. When we export this to excel, it considers as character or string. So we can't perform the data calculation like sum, max, min on those fields in the excel on these fields. I tried, by formating the columns of excel before exporting, but didn't work. I tried changing the destination column datatype in ExcelDestination component, Still not worked. One thing here I noticed was, Soon after executing the package, I checked the data type in the destination component, it again chaged to unicode string. There are null values, but while selecting in the query I replaced them with 0.0 ISNULL(Amount,0.0) Amount. Here are the details: Excel 2003 SQL Server 2005 SSIS package in VS2005. Help. Thanks in advance...
Are the currency fields are numeric in nature? Did you mapped the fields accordingly?
I had a similar problem a few years back. Don't remember exactly what caused the problem, but when I have created a new sheet in SSIS for the source table directly in SSIS and pushed data into that new sheet, everything worked correctly.
There is a connect item which discusses this very issue. Unfortunately there's no solution provided. But you can perhaps wake it up again and get some answers from Microsoft. [
http://connect.microsoft.com/SQLServer/feedback/details/477970/excel-data-types-from-ssis][1] [1]:
http://connect.microsoft.com/SQLServer/feedback/details/477970/excel-data-types-from-ssis
