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.

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

SSIS Export CSV File Task

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:
   

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

3. The query used for this purpose is the following:
   
   SELECT [OrderID] ,FORMAT([OrderDate], 'dd/MM/yyyy') as mydateformat FROM [dbo].[Orders]

   1	SELECT [OrderID] ,FORMAT([OrderDate], 'dd/MM/yyyy') as mydateformat FROM [dbo].[Orders]

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:
   

   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:
   

   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 column name

4. The SQL query will look like this:
   
   SELECT [ShipperID] as {{User::column}} FROM [Northwind].[dbo].[Shippers]

   1	SELECT [ShipperID] as {{User::column}} FROM [Northwind].[dbo].[Shippers]

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:
   
   create table decimalpoint ( decimal nvarchar(50) ) insert into decimalpoint values ('3,45'), ('3,44'), ('6,56')

   1 2 3 4 5 6 7 8 9

   create table decimalpoint ( decimal nvarchar(50) )   insert into decimalpoint values ('3,45'), ('3,44'), ('6,56')

3. Secondly, we will use the following T-SQL Sentence: select replace(decimal,’,’,’.’) as mydecimal from decimalpoint.
   
   select replace(decimal,',','.') from decimalpoint

   1	select replace(decimal,',','.') 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:
   
   create procedure [dbo].[Sales by Year] @Beginning_Date DateTime, @Ending_Date DateTime AS SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date GO

   1 2 3 4 5 6 7 8

   create procedure [dbo].[Sales by Year] @Beginning_Date DateTime, @Ending_Date DateTime AS SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date GO

3. Secondly, we will invoke the stored procedure like this:
   

   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.