How to export data to a csv file using an SSIS package

By:   |   Updated: 2022-04-15   |   Comments   |   Related: More > Integration Services Development


   Free MSSQLTips webinar - "Efficient Monitoring and Management of SQL Server" (click to register)

Problem

You need to extract data from SQL Server and save it to a csv file on a daily schedule. Executing the SELECT statement from a SQL database where the data resides is easy and to copy the data and save it as a csv file is also easy, but if you need to do it regularly, you would prefer to do it with a SQL Server Integration Services (SSIS) package to minimize the possibility of making an error.

Solution

In this tutorial I am going to explain how to use SSIS to extract data from SQL Server and save it to a csv file.

Setting up the Environment

Open Visual Studio, in my case Visual Studio 2019, and create a New Project: Integration Services Project.

When the project opens up you should find a Package open in the designer area, in my case Package.dstx.

On the left side I have the SSIS Toolbox open and on the right side, the Solution Explorer.

Drag a Data Flow Task from the SSIS Toolbox onto the designer area as in the image below.

visual studio edit ssis package

Double-click on the Data Flow Task icon to open up the Data Flow tab.

Drag an OLE DB Source onto the designer area as in the image below.

visual studio edit ssis package

Double-click on the OLE DB Source icon to open the OLE DB Source Editor as in the image below.

connection manager

Click on the New button to create a new database connection. This will open up a Configure OLE DB Connection Manager as in the image below.

connection manager

Again, click on the New button to open up the Connection Manager where you will create a new database connection to the SQL Server that you will be extracting the data from.

Type in the Server name, choose your Authentication and Select or enter a database name where you will extract the data from.

In this case, I will be using the localhost, Windows Authentication and the TestDB database as in the image below.

You can Test the connection to check if the connection is fine and then click on the OK button to create the database connection. See the image below.

connection manager

On the OLE DB Connection Manager window, notice the Data connection created and then click on the OK button as well.

ole db connection manager

On the OLE DB Source Editor, select the Table or View where the data will be coming from or change the Data Access mode to SQL Command, whichever is preferable.

In this case, I will be using a SQL Command.

Type the SQL Command, click the Parse Query to check the syntax of the query, Preview the data if you want and eventually click the OK button.

You OLE DB Source has now been created.

ole db source editor
SELECT [empid] 
      ,[lastname] 
      ,[firstname] 
      ,[title] 
      ,[titleofcourtesy] 
      ,[birthdate] 
      ,[address] 
      ,[city] 
      ,[region] 
      ,[postalcode] 
      ,[country] 
      ,[phone] 
FROM [TestDB].[dbo].[Employees2]

Next, drag a Flat File Destination onto the designer area as in the image below and click on the OLE DB Source icon to show the blue and red arrows.

package flow

Select the blue arrow and connect it to the Flat File Destination.

package flow

Double-click on the Flat File Destination icon to open the Flat File Destination Editor as in the window below.

flat file destination editor

Click on the New button to open the Flat File Format window, select format of the destination file and click on OK button.

See the different Flat File formats that can be used:

  • Delimited
  • Fixed Width
  • Fixed Width with row delimiters
  • Ragged right
flat file format option

Next, the Flat File Connection Manager Editor will open up. Change the name and add a Description, type in the full path of the output file, example: C:\TMP\SSIS\Employees.csv, check the checkbox next to Column names in the first data row and then click the OK button.

flat file destination editor

Now you should click on the Mappings on the right side of the window for the mappings to be created.

Next, you can click the OK button on the Flat File Destination Editor to close it as well.

flat file destination editor mapping

You are done now with the Data Flow tab and click on the Control Flow tab again.

ssis data flow

Now you will drag and drop 2 x File System Tasks onto the designer area as in the image below.

Name them appropriately for example as:

  • Change Filename
  • Delete File

Connect the Data Flow Task with the Change Filename and then the Change filename with the Delete File icon as in the image below.

ssis workflow

Next you will create the Variables that will be used.

Right-click on the back-end of the designer area and select Variables.

Create the 4 different Variables as in the image below. See the breakdown of the variables below the image.

  • EncryptFilename: C:\Tmp\SSIS\Employees_ENCRYPTED.csv
  • Folder: C:\Tmp\SSIS
  • SourceFilename: C:\Tmp\SSIS\Employees.csv
  • TargetFilename:

    Replace(@[User::EncryptFilename],".csv","") +"_"+ Replace(Substring((DT_STR,50,1252)getdate(),1,10),"-","") + "_" + RIGHT("0" + (DT_WSTR,2)DATEPART("hh",GetDate()),2) + RIGHT("0" + (DT_WSTR,2)DATEPART("mi",GetDate()),2) + ".csv"

Example of what TargetFilename will look like: C:\Tmp\SSIS\Employees_ENCRYPTED_20220308_1456.csv

ssis package view variables
ssis package variables

Next, double-click on the Change Filename task to open the editor and do the changes as in the image below.

file system task editor

Also double-click on the Delete File task and do the changes as in the image below.

file system task editor

Your package in the Control Flow tab will now look as in the image below.

ssis control flow

You are ready to execute the package now to see the result.

In the Solution Explorer, right-click the Package and select Execute Package to run the SSIS package.

execute ssis package

When the package runs successfully, it should look as in the image below.

ssis package run status

Open File Explorer with a Preview on the right-side and then right click on the file created to see the contents in the preview.

review exported data
Next Steps



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Jan Potgieter

Jan Potgieter has years of experience in Information Technology which ranges from Unix Administration through System Administration and development, Web and Database Development and Administration and applying Agile methodologies.



View all my tips


Article Last Updated: 2022-04-15

Comments For This Article





download














get free sql tips
agree to terms