asked
Export to Excel or CSV using SSIS
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?
Are you using the flat file connection manager? Instead of having a pre-made CSV, let SSIS generate out a CSV for you with the data. It should be an extremely simple task to export out data to CSV using SSIS. If this is a one time method, then just run your query and save to CSV :-) Though I'm assuming this will be a recurring process.
Yes this will be a process that will occur every Monday. I am using the Flat File Connection and I tried Excel also. The extraction to CSV is definitely easy but the comma in my data is causing the issue. It shifts all columns on every comma seen. I need a work around on this piece.
As promised, here is the blog post with step by step. [Using an Excel Destination in SSIS with x64](
http://wp.me/p14wce-2W)
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.
Perhaps I am misunderstanding what you are trying to say. CSV files will happily support commas in the data field as long as their is either some sort of text quoting or character escaping is allowed. The most common version of this is using double quotes " as a text signifier and wrapping any text fields that might contact a comma in the quoting.
Is Donations the name of the worksheet that you created when you added the Excel destination object? One of the things I have learned is you can make changes easily to the column names, or worksheet and the package work properly. Do this, create a new excel file, and capture the create statement that SSIS uses to create the worksheet. Paste that into your SQL TASK for creating the worksheet, and also in the first SQL Task to DROP the table. Then change the create to drop and remove all the table creation portion of the script in your drop task. Now try to debug your package.
Yes text qualifiers work great if you have end users who can work Excel well enough to specify how the file should be sorted and opened. What I was gathering from @noonies is what was needed was a true Excel file. When it was attempted to just pipe it out as CSV to be opened in Excel with no effort the data was being shifted. I haven't tried it with recent versions of Excel but can you create a CSV with text qualifiers and Excel open it properly without having to go through a wizard of sorts to specify how the columns should be separated?
@TRAD - No go! I get the following error: [Execute SQL Task] Error: Executing the query ""DROP TABLE 'Sheet1$' GO"" failed with the following error: "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
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
