February 5, 2008 at 6:18 am
I have a SSIS package that sends results to an Excel destination with no problem on the first pass(with the create table ddl).
Set up:
After the first load I deleted data rows and made a copy of the file and moved to a template directory.
Each time I run package I delete and replace primary file with template file so as not to append the data.
The package inserts data properly with the exception of adding an apostrophe to all the numbers thus making Excel think its a number stored as text.
I have preformatted my template columns as numbers and this does not work.
Everytime I rebuild this and run for the first time it inserts the correct way. Each subsequent insert results in same problem when I delete the rows. If I append it is not an issue...........
Any help would be welcomed
Thanks
February 6, 2008 at 2:34 am
Seems to be the wrong forum but:
IMHO Excel sources and destinations are a mess :sick:.
Look at the metadata SSIS determines for your excel destination. It does not matter which cell format you define in excel. SSIS scans the data in the sheet. I'm not sure but I think that it looks at the last row in the sheet that does contain anything (data and formatting) and decides then whether to adress it as string, number or whatever.
So take care to leave a row containing numeric data (it may even be hidden from view) and SSIS should be able to determine correct data types.
February 6, 2008 at 5:45 am
Ahh it works, never thought about adding a template line to my template file, guess I could store on a template server...Never the less thanks for your input and solution.
September 15, 2008 at 5:22 am
This oily work around is amazing!! :w00t: But why Microsoft wants to drive us mad???? :angry:
By the way, thanks for your trick Alexander!! :kiss: 😀
March 10, 2009 at 9:50 am
Bingo Alex ---
Simply superb it works perfactly.. Thanks a lot.
Hary 😉
Thanks a lot,
Hary
May 6, 2009 at 3:12 pm
This solution will not work for me as I cannot have an extra row at the top, regardless of it being hidden or not. Are there any other work arounds?
June 30, 2010 at 2:54 am
Thanks Alex for the solution. Its working 🙂
June 22, 2011 at 2:18 am
No, it's not a solution but having looked quite a bit this appears to be the only workaround. I'm using a Script Task to come along afterwards and delete the dummy rows afterwards.
I like lucazav's comment above - are MS trying to drive us mad? Outputting numbers to Excel seems like such a basic requirement - what a fuss!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply