I'm trying to export data from SQL to Excel or even a CSV. The issue I am having is that if I export to EXCEL the DELETE option is not allowed to overwrite the data in the worksheet. The other issue I'm having is when I extract the data in a CSV the comma found in the spreadsheet shifts the data due to the delimiter being comma(,). I have tried using Tab but this puts all my columns and data into one BIG column. I really need the commas in the data! I'm not sure what else to try. I think I tried everything. Does anyone know of a workaround or how I should handle this? Could I maybe delete the file and recreate using SSIS as a workaround?
You can't have a comma in data in a CSV file. Otherwise the comma will shift the data. If you need a common delimited file try using some other separator. I know you mentioned TAB put it all in one big column. Can you explain? You should be able to open a tab separated file with EXCEL with no issue, you have to specify that it is tab separated when opening. If the requirement is to get the data from SQL into EXCEL then just chose an EXCEL destination, then comma's won't matter at all. The way to get around the appending of data into EXCEL is to have a SQL Task where you drop the worksheet in the excel destination and then another SQL TASK to create the worksheet. Then you run your dataflow task. Explanation In your Control Flow a SQL TASK. In the SQL Statement chose EXCEL, in Connection chose your Excel Connection Manager, SQL Source type is Direct Input, SQLStatement = 'DROP TABLE 'NAME OF YOUR WORKSHEET' GO. Add another SQL TASK, same items as above but your SQLStatement will be a create table statement to recreate the worksheet. Some thing like CREATE TABLE 'NAME OF YOUR WORKSHEET'('NAME' VARCHAR(16), 'ACCOUNT'DECMIAL(9,0)) GO. Connect your first SQL TASK to your second, then the second to your control flow. The create table statement should be the same one that is created when you create your Excel destination. Let me know if you have trouble doing this and I will either find you some links or try to post some screen shots.
if this is a recurring job follow these steps: step 1: Create SSIS package based on your requirements and don't execute the package yet. step 2: Go to the path where you have saved the excel file and save a copy as ex: XYZtemplate.xls. you need to have XYZ.xls and XYZtemplate.xls files at that location. step 3: create a batch file as below at the same location where you have saved the excel files. DEL C:\MyDocs\ABC\XYZ.xls COPY C:\MyDocs\ABC\XYZtemplate.xls C:\MyDocs\ABC\XYZ.xls step 4: Finally add an execute process task as first step in your package then the rest. step 5: In the execute process task editor go to the process and in the executable call the above batch file. C:\MyDocs\ABC\XYZ.bat