How to convert SQL to CSV in SSIS

How to convert SQL to CSV in SSIS

There are several tricks related to convert SQL to CSV. For example, dates, working with variables, converting text, working with stored procedures. In this article, we will work with several tips to convert SQL Server data to the CSV format using CSV.

Prerequisites

Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
  4. (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.

Prerequisites

Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
  4. (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.

For this article, we will use the following ZappySys component:

 

How to convert SQL to CSV using specific dates

  1. First of all, in order to convert the date to a specific format, you can use the Export  CSV task.
  2. This task allows converting SQL Server tables into CSV. We will show how to set the date to a specific format:
    SSIS export in a custom format

    Export from SSIS SQL Server to CSV in a specific date format

  3. The query used for this purpose is the following:
  4. As you can see, you can send custom queries in the Export CSV task.
    In this example, we are taking advantage of the T-SQL Format function which allows converting the date in a custom format. In this example, we are converting the OrderDate column into the format dd/MM/yyyy.
  5. Also, specify the destination path:
    SSIS path to export a SQL Server query

    Export path csv

  6. Finally, run the package. In this example, we converted the date to the format dd/MM/yyyy.

How to convert SQL Server to CSV using dynamic column names

  1. A common question is to have column names based on SSIS variables. This time will show how to do this.
  2. First of all, we will need an SSIS variable that will contain the column name:
    Variables used for dynamic column names

    SSIS variable with a column name

  3. Secondly, we need to use the value of the variable as the column name. We will use the alias (AS in SQL) to create a custom name.
    SSIS columnn name based on variable

    SSIS column name

  4. The SQL query will look like this:
  5. The previous example used the SSIS variable named column to store the name of the column. If the Variable value is column1, the name of the column will be column1.

How to convert SQL Server to CSV to change the decimal point to a decimal comma

  1. Most of the countries in the world use the decimal point or the decimal comma. For decimals, some countries use the comma and other the period. In this new example, we will show how to convert decimal data into a comma.
  2. In order to convert, we will convert and work with a text data type. We will use the following script to create a table with data:
  3. Secondly, we will use the following T-SQL Sentence: select replace(decimal,’,’,’.’) as mydecimal from decimalpoint.
  4. In addition, we will create a path to export.
  5. If everything is fine, the CSV file created will convert the period decimal to a coma decimal.

How to export data from a parameterized SQL Server stored procedure into a
CSV file

  1. In this new example, we will use a stored procedure with parameters and store the results into a CSV file.
  2.  First of all, we will use the following parameterized stored procedure:
  3. Secondly, we will invoke the stored procedure like this:
    store procedure into csv

    invoice stored procedure and export to csv

  4. Finally, if everything is fine, the data will be exported to the CSV file after running the package.

Conclusion

In this article, we show how to export to CSV from SQL Server using different date formats. We also learned how to change the types of decimals used. For more fun, please download our components.

 

 

Posted in SSIS CSV Export Task and tagged , , , .