Hi I am using SQL Server 2005 database and SSIS to load data into excel. My requirement is: The data in the excel is suppressing leading zeros which I dont want to do. Ex: The column value in the original table was '00001234' however in the excel it loads as '1234'. Data source: Table (created in SQL Server 2005 ) Destination: Excel (97-2003) Connection Manager: FlatFile FlatFile Connection manager properties: Format: Delimited; Text Qualifier: <none>; header row delimiter: <cr><lf>; Column Delimiter: {tab} The data type of the column that I am using (at source: varchar), excel file (DT_STR), dataflow task I/P, O/P Parameters: DT_WSTR I tried following options: By changing the columns in the excel template to text, updated output datatype to DT_Text/DT_NText but no luck. Please let me know if I need to do something else? Thanks PK

Excel does not play well with SSIS, especailly when trying to push data to it. This is very surprising to me because both are Microsoft products. Maybe try forcing in the preceding single quote character before the leading zeros, so that 00001234 is actually pushed to Excel as '00001234. Use a Derived Column to do that. Hope that helps. BTW, What version of Excel and SSIS are you using. I think they fixed some stuff in later version of both. Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Todd: Thanks for your response. The versions I am using SSIS - 9.0.5000 Excel - (97-2003) and 2007 ( I tried both versions). I tried the way you suggested and yes, now all the leading zeros appears preceding a single quote . ex: '00001234 Is there any other way to not to show single quotes in excel. Thanks

SQL 9, that would be 2005. Any chance of upgrading to 2008? Let me ask you this: Why exactly do you need the output in Excel? I also noticed that you said this: Destination: Excel (97-2003) Connection Manager: FlatFile What do you mean by a "Flat File Excel Conneciton"? Could you use a TXT or CSV flat file connectin instead of Excel?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

The client wants it to be as 'Excel'. Yes, I am using a FlatFileconnection manager to generate output excel file. When creating the connection manager(which requires some template so I have put all columns in a txt file and selected the properties as mentioned above. (Format: Delimited; HeaderRow Delimiter: CR|LF etc...). But when I am writing the output file I made the file extension to .xls. when I viewed ouput file in text pad it did show the preceding single quote. Everything is fine except extra single quote

we have been using this process (FlatFile connection manager) for all other packages so wanted this to be in sync with them. However this particular problem arised (not retainng leading zeros) to this requirement itself.

Why not use an Excel connection manager? Not for nothing, but even though you specify a file as a .xls extension, that doesn't make it an Excel file. It's still a flat file. Yes, you can open it in Excel becuase Excel knows how to interpret things in comma separated value format. Painting racing flames and a number on a 5 year old sedan doesn't make it a race car. (Hey, Daytona's in less than two weeks, OK?) I'm willing to guess that if you use a proper Excel destination (either native Excel, or the OLE DB Connector for Excel) and you passed it a value of 000012354, that Excel JUST MIGHT display it exactly as you have it.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

I am trying to use Excel Destination and Excel Connection Manager. The package is being called from a job. When I run the job it throws following error: Option "Source=D:\SAMPLE_FILES\Archive\MyFolder\pk.xls;Extended" is not valid. The command line parameters are invalid. The step failed. The Data source connection string from the excel connection manager is: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\DSS_FILES\Archive\3210\konda.xls;Extended Properties="EXCEL 8.0;HDR=YES"; does it need to have any specific drivers or Connection String itself is incorrect?

Your connection string looks good. Running as a jog via SQL Agent? On the server or your desktop? If on the server, you may need to download the OLE DB Provider for Office 12, which will work with Excel 8.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Thanks..Yes, its on the server. How do I justify that those drivers are not there in the server?

Tell (don't ask) your server administrtors to install this compoenent: http://www.microsoft.com/downloads/en/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en Install it locally as well so you can design against it. Change your Excel connectin to OLE DB for Microsoft Office Access 12. Justification is that EVERYBODY uses Excel and you need to be able to interface with it.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Thanks for your suggestions. I was able to get the results to excel however, the old data/file is not getting overwritten, which I want to do. Each time I run it should overwrite old data. Also I observed, the data is written after 100 blank lines in excel.

Sounds like you are appending data in your Excel file. When working with Excel, I sometimes find it good to have a Template Excel file saved on a network drive, and then inside the SSIS Package, I use a File System Task to copy this file to the working folder where SSIS will populate it. The template file is already set up with proper Sheet name(s) and column names.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Hi Todd C, I am doign the same, Copying Template from Network location and loading the data but the issue i am getting is, its loads and appends the data below te formated template area/range. How can i make it start writing from First Row. Please be aware of the fact that i load few colums and has got drop downs in other columns. I am not loading anthing in Drop Down Columns. Can you please comment on thisSaadat

If this was a SQL table that already had rows of data in it, what would be your expectations as to the status of the existing rows AFTER to SSIS package runs? The rows would still be there, wouldn't they? So why should Excel be any different. The solution is to edit the Template file and remove those rows and re-save the template. Now, when you do the file system task of copying the template file, you will get a table with no rows to start with. Hope this helpsTodd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.