Decimal places rounded up when trying to load Excel file using SSIS script task

Question

Dear all,

when I tried to use script task to import excel data into table

in source file the data as below, the cell data formats are integrate, but when click it is number format with 4 decimal places

when I tried to import these data into a data table and tried to show them, and found as below, Is there a way to show decimal places?

Many thanks,

and the C# as below,
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace ST_877e68d5e4764a9f9c1583d1994eb1d9
{

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

public void Main()
{
// TODO: Add your code here

        string FolderPath = Dts.Variables["User::FolderPath"].Value.ToString();  

        string fileFullPath = FolderPath + "\\" + "test.xlsx";  

        SqlConnection myADONETConnection = new SqlConnection();  
        myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);  

        //Create Excel Connection  
        string ConStr;  
        string HDR;  
        HDR = "YES";  
        ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties=\'Excel 12.0;HDR=" + HDR + ";IMEX=1'";  
        OleDbConnection cnn = new OleDbConnection(ConStr);  

        OleDbCommand oconn = new OleDbCommand("select * from [Advanced Optics$A5:V]", cnn);  
        OleDbDataAdapter adp = new OleDbDataAdapter(oconn);  
        DataTable dt = new DataTable();  
        adp.Fill(dt);  
        cnn.Close();  

        //Variables  
        int ColCount = dt.Columns.Count;  
        int RowCount = dt.Rows.Count;  
        string Val;  
        int RegionIndex;  
        string RegionTmp;  


        //Create new DataTable  
        DataTable dtN = new DataTable("test");  

        dtN.Columns.Add("col1", System.Type.GetType("System.String"));  


        //get Data list  
        for (int i = 2; i < RowCount; i++)  
        {  
            for (int j = 2;j < ColCount; j++)  
            {  

                //Get Val  
                Val = dt.Rows[i][j].ToString();  
                MessageBox.Show(dt.Rows[i][j].ToString());  
                //insert data into Data table  
                    dtN.Rows.Add(col1);  

            }             
        }  
          
        //Load Data from DataTable to SQL Server Table.  
        using (SqlBulkCopy BC = new SqlBulkCopy(myADONETConnection))  
        {  
            BC.BulkCopyTimeout = 60000;  
            BC.DestinationTableName = "[dbo].[test]";  

            BC.WriteToServer(dtN);  
        }  

        Dts.TaskResult = (int)ScriptResults.Success;  

}

    enum ScriptResults  
    {  
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,  
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure  
    };  
    #endregion  

}
}

Answer 1

I want to provide another view, after reading your codes I find that you use String and Int type to process all your data, maybe you can change them into floating-point numeric type such as float, double and decimal. I post a screenshot of Microsoft C# docs below for you to refer. If you want to know more about these numeric types, please click here and view the content in this section: value-types