Leading Zeros missing while exporting Zipcodes to Excel

Question

I am trying to export zipcodes from table to Excel file, it seems leading zeroes are missing. Any help is much appreciated. I googled it didnt find an answer and checked msdn no one gave correct answer for this issue. For now i am Querying using Right function but i need to automate this.

SQL server Version: 2005

Excel Version : 2003(.xls)

Thanks

---

s

Answer 1

use DT_NUMERIC as the datatype and also set the conn string of Excel to include IMEX=1 directive

---

Arthur My Blog

Answer 2

Where are the leading zeroes missing? Already in the dataflow after selecting the data, or in the Excel file?

---

MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

Answer 3

When i changed the data type to unicode string in Derived Column Transformation leading zero is captured, where as leading zero is missing when i use data conversion transformation. What is the difference?  

---

s

Answer 4

Both the Derived Column Transformation and Data Conversion Transformation components provide with a similar functionality. But in the case of using the Derived Column Transformation one can also transform the data (using SSIS Expressions), so in essence, there is no difference.

The difference becomes apparent when you transform, e.g. most times the data loss occurs at the transformation part.

Not sure what was involved in your case, perhaps when you did the RIGHT function you operated on an INT which already came with the leading 0 dropped.

---

Arthur My Blog