Generate the excel report with currency symbol in SSIS package using Data Flow Task

We have a requirement to generate the report that reports having multiple currencies with a currency symbol.

The report looks like as below    

We have written the SP which returns this result set.

We are using the AdventureWorksDW2017 database for the demo.

Below is the SP

Create PROCEDURE [dbo].[FactInternetSales_rpt]    AS BEGIN  select replace(dp.EnglishProductName,'''','')  as ProductName ,N'₹'+ cast(SUM(fis.SalesAmount) as nvarchar(50)) as SalesAmount_Indian_rupee ,N'$'+ cast(SUM(fis.SalesAmount)*0.013 as nvarchar(50)) as SalesAmount_USD_dollar ,N'$'+ cast(SUM(fis.SalesAmount)*0.017 as nvarchar(50)) as SalesAmount_Canadian_dollar ,N'€'+ cast(SUM(fis.SalesAmount)*0.011 as nvarchar(50)) as SalesAmount_European_euro ,N'¥'+ cast(SUM(fis.SalesAmount)*0.087 as nvarchar(50)) as SalesAmount_Chinese_Yuan_Renminbi ,N'₨'+ cast(SUM(fis.SalesAmount)*2.12 as nvarchar(50)) as SalesAmount_Pakistani_rupee from FactInternetSales fis join DimProduct dp on dp.ProductKey=fis.ProductKey group by dp.EnglishProductName  END;

 Here in this SP we have added currency symbol and exchange rate manually. But we have a table where we have store the currency symbols and another table for the exchange rate. We need to use that that to prepare using both tables.

Script to insert the currency code andsymbol Read here:

https://bageshkumarbagi-msbi.blogspot.com/2021/06/sql-script-to-insert-world-currency.html

 Now we are taking the Data Flow Task.  

Taking Source as OLEDB Source and selecting Data Access Mode as Sql command text

   

Calling the SP to Generate the Resultset.

We can see the data after clicking on the preview button.

   

Click ok.

Now taking the Destination as Excel and creating the connection manager.  

Selecting the Data access mode as Table and clicking on the New button to create the table in the excel.

  

By default are getting the below tale structure.

CREATE TABLE `Excel Destination` (     `ProductName` LongText,     `SalesAmount_Indian_rupee` LongText,     `SalesAmount_USD_dollar` LongText,     `SalesAmount_Canadian_dollar` LongText,     `SalesAmount_European_euro` LongText,     `SalesAmount_Chinese_Yuan_Renminbi` LongText,     `SalesAmount_Pakistani_rupee` LongText )

Now we need to make the changes in the data type and table name as below.

CREATE TABLE `FactInternetSales_Report` (     `ProductName` LongText,     `SalesAmount_Indian_rupee` nvarchar(100),     `SalesAmount_USD_dollar` nvarchar(100),     `SalesAmount_Canadian_dollar` nvarchar(100),     `SalesAmount_European_euro` nvarchar(100),     `SalesAmount_Chinese_Yuan_Renminbi` nvarchar(100),     `SalesAmount_Pakistani_rupee` nvarchar(100) )

Click ok. 

Selecting table name.

Mapping the columns.

  

Click ok.

Now package is ready to run.

Running this package.  

Report is generated successfully. 

See the report.  

Get the expected result.