Hi experts,
I revised my excel template file by changing 3 columns from General to Numeric 0 decimal.
But when I run the package, the report it generates still has the 3 columns defined as General
I need the columns to be Numeric/Decimal so the users can total on that column easily.
I have remapped the columns in the Excel Destination task.
Also redeployed the package of course.
The latest version of the template file IS being picked up. In the blank rows, the 3 columns are defined as Number. But in the populated rows....the 3 columns that contain numeric data are still defined as "General" in the report that is generated.
Do I have to use a Table to be able to have numeric cells?
This is very odd. Does anyone know of anything else I can do?
Thanks.
It seems it needs to be done in conjunction with a particular Access Mode.
My guess is that SSIS is applying the General format as it's the best option for Excel data. I don't recall it having the feature to set a cell format on output (I could very well be wrong). Generally SSIS isn't meant to export formatted/styled output, that would be more properly done via SSRS.
Failing all of that, the only other reference I could find that sounds relevant:
It doesn't say whether that applies to importing or exporting, or both. But if it's not something that you can get the template to do then I think you'll need to add a script task to change cell formatting.
Just to cover all bases, are the columns you're trying to format as decimal indeed numeric data, or are they string? Is the transform converting them to a numeric type?
"It seems it needs to be done in conjunction with a particular Access Mode."
I;m already using the mode the article suggest.
Based on the lack of replies to my question (other than yours of course) and that all the tips I've found
have not worked...... my conclusion is that there is no path to victory here.
The script task trick may work but in that article he is changing only cell 15. I have 3 cells to change to Number in all detail lines. ie there are 3 columns on each row that will contain numeric data.
This shouldn't be so difficult. We have 2 Microsoft products SQL SERVER/SSIS and Excel that lack compatibility.
Thanks again.
I think I mentioned this in an older thread, but if you have very simple data transforms/pipelines, you may be better off doing this as either an Excel macro or with a data refresh task in the worksheet. If the formatting is that important, that's going to be the easiest way.