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

Hi SweetsUK,

According to your description, you want to extract the data returned by a stored procedure to a csv file in SSIS.

To achieve your requirement, we can use an OLE DB Source and a Flat File Destination within a Data Flow Task. For more details, please see:

1. Drag a Data Flow Task to Control Flow Task.
2. Create an OLE DB Connection Manager that connects to the corresponding server database.
3. Create a Flat File Connection Manager that connects to a csv file.
4. Drag an OLE DB Source with the OLE DB Connection Manager already create, then change Data access mode to SQL command, type the query that execute the stored procedure in SQL command text.
5. Drag a Flat File Destination with the Flat File Connection Manager that connects to the Source component, then click Mapping pane to finish the mapping.
6. After executing the Data Flow Task, a csv file with the data returned by the stored procedure would be created.

Thanks,
Katherine Xiong