Hi ALL,

It is a just a simple SSRS report, but the column header needs to be like user.Email, user.FirstName, user.Surname etc. When downloading the file in csv the dots get replaced by underscore, which is unacceptable by business. This is a known SSRS issue.

So now we have decided to move onto SSIS package.

I am just struggling to do a simple task of executing a Stored procedure (using ExecuteSQLTask component) and writing it into a flat file. Thought this would be an easy & quick fix.

Can someone please help me with the steps to follow if I want to Exec a S_proc and write the output into a csv file please ?

Hi SweetsUK,

here is a nice blog post on how showing several options

http://www.codeproject.com/Questions/546777/HowplusToplusExportplusDataplusToplustheplus-csvpl

You can try the following to get  the required format of column names.

1.Load the data with columnn names into temp table.(or use SP to SSIS/SSRS)

2. Load the data from temp table to CSV file.

3.Here step2, in SSIS package change the column names as given below for Flat file conn  manager.

   3.1 Create SSIS package

            Source: Temp table  (SQL Server)
            Target:  Flat file (CSV ext file)
           In SSIS package, Flat File conn manager editor---->Click on Advanced---->right hand side, "MSC"  ,         
           Name--> modify the each column name (example .... id as USER_ID, name as USER_Name ....etc)

  3.2 Save SSIS package and run.

4. Output CSV file coulmn names will display lik e
    USER_ID, USER_Name
    1              aaaaa

    2              bbbbb

    3              cccccc

thanks

There are MANY ways to do this.

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\;HDR=YES;FMT=Delimited','SELECT * FROM [FileName.csv]')
SELECT Field1, Field2, Field3 FROM DatabaseName

Also, check out these links.

http://bpmdeveloper.com/sql-server-export-wizard-csv/

http://www.codeproject.com/Tips/1017732/SQL-Server-export-to-CSV

https://msdn.microsoft.com/en-us/library/ms175937.aspx?f=255&MSPPError=-2147217396