Generate the excel report with currency symbol in SSIS package using Script 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

In the previous post we have seen how to generate the same report using the Data flow task. But if we want to add custom things in the report we need to use Script task.

Read here :  Generate the excel report with currency symbolin SSIS package using Data Flow Task

https://bageshkumarbagi-msbi.blogspot.com/2021/07/generate-excel-report-with-currency.html

Let’s see how we are generating this report using Script task.

Now we are taking the Script task.  

Now we are creating the Ado.net connection.

  

Now we click on the Script edit button.

We are wring the below code.

public void Main()          {              string datetime = DateTime.Now.ToString("yyyyMMdd");             string FolderPath = @"H:\SSIS1\Desc\";             string File_Name = "FactInternetSales_Report";             try             {                 SqlConnection myADONETConnection = new SqlConnection();                 myADONETConnection = (SqlConnection)(Dts.Connections["Ado_Dot_net_Conn"].AcquireConnection(Dts.Transaction) as SqlConnection);                 string ExcelFileName = "";                  ExcelFileName = File_Name + "_" + datetime;                  OleDbConnection Excel_OLE_Con = new OleDbConnection();                  OleDbCommand Excel_OLE_Cmd = new OleDbCommand();                  string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FolderPath + "\\" + ExcelFileName                    +                   ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";                  SqlDataAdapter adapter = new SqlDataAdapter("FactInternetSales_rpt",myADONETConnection);                  DataSet ds = new DataSet();                  adapter.Fill(ds);                  Excel_OLE_Con.ConnectionString = connstring;                  Excel_OLE_Con.Open();                  Excel_OLE_Cmd.Connection = Excel_OLE_Con;                  Excel_OLE_Cmd.CommandText = "Create table [" + ExcelFileName + "] ( [ProductName] varchar(100),[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))";                  Excel_OLE_Cmd.ExecuteNonQuery();                  foreach (DataTable table in ds.Tables)                  {                      String sqlCommandInsert = "";                      String sqlCommandValue = "";                      foreach (DataColumn dataColumn in table.Columns)                      {                          sqlCommandValue += dataColumn + "],[";                      }                      sqlCommandValue = "[" + sqlCommandValue.TrimEnd(',');                      sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2);                      sqlCommandInsert = "INSERT into [" + ExcelFileName + "] (" + sqlCommandValue + ") VALUES(";                      int columnCount = table.Columns.Count;                      foreach (DataRow row in table.Rows)                      {                          string columnvalues = "";                          for (int i = 0; i < columnCount; i++)                          {                                int index = table.Rows.IndexOf(row);                              columnvalues += "'" + table.Rows[index].ItemArray[i] + "',";                          }                          columnvalues = columnvalues.TrimEnd(',');                          var command = sqlCommandInsert + columnvalues + ")";                          Excel_OLE_Cmd.CommandText = command;                          Excel_OLE_Cmd.ExecuteNonQuery();                      }                      Excel_OLE_Con.Close();                  }                  Dts.TaskResult = (int)ScriptResults.Success;              }             catch (Exception exception)              {                  using (StreamWriter sw = File.CreateText(FolderPath + "\\" + "ErrorLog_" + datetime + ".log"))                  {                      sw.WriteLine(exception.ToString());                      Dts.TaskResult = (int)ScriptResults.Failure;                  }              }          }

Now we are running this script task.

Package executed successfully.

  

And report generated successfully.  

See the report.

  

Get the expected result.