I have excel as source my problem is one of the column has values as 01, 02 ,03 but when i am trying to export it i am getting as 1, 2, 3 . i need it in the same form like 01, 02, 03. my preview is not showing the zeros in front of the number which i want to export. Thanks

Please check the Data Type of your column in which you are inserting data. If the column is of type Int/Decimal/Numeric/Bigint/Tinyint then it will not support previous zero in values. To solve the problem keep the field as Varchar

Hi, As Sandeep mentioned you can use a varchar to do export that as required since it takes as string datatype. Check this thread where they discusses some of the excel exporting data format issues. Hope this will be useful. http://beyondrelational.com/ask/sudeep/questions/12/data-format-issue-while-importing-data-from-excel-to-sql-server-using-ssis.aspxThanks Karthikeyan Anbarasan http://f5debug.net/

If the data type is numeric then you won't be able to import it with the leading zeros. The Excel driver determines the datatype automatically by examining the first 8 rows. With an expression in a Derived Column you can create a new column with leading zeros. These two expression will add up to 5 leading zeros to a number. So "123" becomes "00123": RIGHT(("00000" + [STRING_COLUMN]), 5) REPLICATE("0", 5 - LEN([STRING_COLUMN])) + [STRING_COLUMN] Note: you will have to convert a numeric field to string field first with DT_WSTR or DT_STR.Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

The Excel driver determines the datatype automatically by examining the first 8 rows. Unless you change the registry setting TypeGuessRows for the JET provider. If you set it to 0, it will scan all rows. There might be a slight performance decrease, but the results will be more accurate.MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

The Excel driver determines the datatype automatically by examining the first 8 rows. Unless you change the registry setting TypeGuessRows for the JET provider. If you set it to 0, it will scan all rows. There might be a slight performance decrease, but the results will be more accurate. MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends... Agree that it will give you a more accurate guess, but I think that with mixed datatypes it will still use the majority rule... Any experience with ZERO and very large excel files... wont that be very very slow? *OFF TOPIC* Won't it be better if we all just stop using excel as a source in SSIS :-) Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Agree that it will give you a more accurate guess, but I think that with mixed datatypes it will still use the majority rule... Yes, but at least it will have detected that there are mixed data types :) If IMEX=1, the result will be the string datatype, which can handle everything. Any experience with ZERO and very large excel files... wont that be very very slow? What do you mean exactly? *OFF TOPIC* Won't it be better if we all just stop using excel as a source in SSIS :-) +1!MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

Any experience with ZERO and very large excel files... wont that be very very slow? What do you mean exactly? Haven't test it myself, but if the excel file has like 2 million rows and a lot of columns... I expect it will be very slow to scan the complete excel file if TypeGuessRows = 0. And I also expect that a lot of administrators won't like it if you change registry values on THEIR production machines :-(Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Agree that it will give you a more accurate guess, but I think that with mixed datatypes it will still use the majority rule... Yes, but at least it will have detected that there are mixed data types :) If IMEX=1, the result will be the string datatype, which can handle everything. Agree Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com