Re: How to format columns when exporting to excel in SSIS

thanks for this blog. i set it up exactly the way you described it but in the last step when selecting the name of excel sheet i get a microsoft.jet.oledb.4.0 provider not registered on the local machine error. i have the latest mdac installed. please let me know if you know how to solve it.

Re: How to format columns when exporting to excel in SSIS

I was hoping to find a way to avoid using a template file to create a new file (i.e. a way to replace existing data with new data), and came across this blog.

I've got pretty much the same process, but - even though I format my TotalSales column as Number (0 decimals), and have DT_I4 as source, and have changed the mapping in advanced editor on the excel destination input - external column to DT_I4, when I run the package, it is cahnged to WSTR, and the data is written as text. The column even retains its formatting on the cells - but the data is text. Any ideas?

Re: How to format columns when exporting to excel in SSIS

Hi!
I have a problem with creating the Excel template file!
When I use the template I created in Excel 2002 I always get a fatal error message, telling me to check the metadata validity. So far I figured out that SSIS uses version 7 excel files (when selecting Microsoft Excel 97-2005 for version in the Connection Manager), and when I am creating the template in 2002, the version is always 9, even when saving as an Excel version 4 worksheet or whatsoever.

Do i have this problem only? What could be done?
Thanks for any help!

Re: How to format columns when exporting to excel in SSIS

I use the advance editor to change the data type mapping and still it does not keep and also the changes in type on external columns does not stick and it goes back to WSTR. It looks like template only retains the font and not the type.

Re: How to format columns when exporting to excel in SSIS

What version of Excel are you using to create the Excel file? You save it in Excel, then it works for you in SISS? Anyway to answer your problem: You cannot define the external data type of the Excel column in SSIS. You can change it, but at Run time the external JET driver, that actually reads the Excel will allways reset the data types. For this the JET driver takes the first 8 rows as sample and from this it determines wether the rows are of a numeric, text, date, etc type. This becomes a problem, when you have a mixed typed coulmn, that has text and numeric values too. Like if you have just numeric values in the first 8 rows and afterwards you have string values too, then the type will be set to a numeric type and all the sting type values will be lost. The workaround this is to increase the number of rows that are sampled from 8 to a higher number. This all can be done only in the registry.

Re: How to format columns when exporting to excel in SSIS

blah

Re: How to format columns when exporting to excel in SSIS

blah

Re: How to format columns when exporting to excel in SSIS

When using the excel destination in SSIS, the default max width of the column is set to 255, is there any way possible for me to change this width limit, as the data that i am retrieving from the database is greater than 255 and the component fails as a result of truncation error.
Any help in this regard is appreciated.
thanks in advance

Re: How to format columns when exporting to excel in SSIS

How can i handle the maximum number of rows per worksheet wich is 65536 when exporting data from SQL to Excel ?

Re: How to format columns when exporting to excel in SSIS

Thanks for the blog, this is just what I needed! However, I get en empty row between my row with column headers (1st row) and the actual data that is exported. This means that row 2 is empty (it contains formatting) and the data starts at row 3. How do I get rid of the empty row 2? I need to solve this soon, so any help/suggestion is very welcome.

Re: How to format columns when exporting to excel in SSIS

Use the empty Column as your totals row. That way you can dynamically have sum(A1:A100000) and without knowning the number of rows in your data you have a total and use for blank row. Or just hide the row.

Re: How to format columns when exporting to excel in SSIS

Can someone tell me that if my destination file is created at runtime in the above case then how o go about it???

Re: How to format columns when exporting to excel in SSIS

Re the problem logged above on 17.08.2006 "when I run the package, it is cahnged to WSTR" this issue is that the connection manager is looking at the destination excel file at runtime and seeing blanks in the target cells and regardless of the format of those cells, interpreting the balnks as text and changing the data format to text on output. To solve I just added 8 rows of numeric data in the spreadsheet, then numeric should be posted ok (then just ignore those 8 rows of dummy data in subsequent analysis. Pants I know but it works.

Re: How to format columns when exporting to excel in SSIS

I am doing a very simlar kind of stuff, but am using colored headers for format. So it leaves one blank row in the middle in betweenthe data and header.

For more details ,
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2904976&SiteID=1


Any thoughts in this regard would be appreciated.

IF you got queries please email me h.zulfi@gmail.com

Re: How to format columns when exporting to excel in SSIS

"this issue is that the connection manager is looking at the destination excel file at runtime and seeing blanks in the target cells and regardless of the format of those cells, interpreting the balnks as text and changing the data format to text on output. To solve I just added 8 rows of numeric data in the spreadsheet,"

holy... this is retarded... it's my first time learning + using SSIS and I keep running into these sort of things and I have to google workarounds... arf... I gues in this case the real culprit is JET driver... but still.... its starting to annoy me already... almost feel like writing good old fashion scripts and schedule them as jobs ...

Re: How to format columns when exporting to excel in SSIS

When I copy my temp file to my destination file. It doesnt take the format of my temp file. For example, I want to set a column to have two decimals. Thsi works on my temp file but not in my destination file. Any help ?

Re: How to format columns when exporting to excel in SSIS

I have written an aricle on this. Kindly check it.

http://venkattechnicalblog.blogspot.com/2008/09/excel-column-is-greater-than-255-in.html

Regards,
Venkatesan Prabu .J

Re: How to format columns when exporting to excel in SSIS

Thanks that was very helpful

Re: How to format columns when exporting to excel in SSIS

I don't believe the example is correct. Certainly it is incorrect as shown. In Excel, numeric data by default is always *right* justified, and text data is always *left* justified.

In the example displayed, I notice two things: first, all values are left-justified, suggesting Excel thinks everything is text. Second, the little green flags in the upper-left corners of "TestNumber" and "TestFloat" are what Excel displays when you have a text value that Excel believes is actually numeric data.

Sorry. I was hoping this was the answer to a very annoying aspect of SSIS/Excel.