Welcome To TechBrothersIT: How to read Cell Value from Excel by using Script Task in SSIS Package

How to read Cell Value from Excel by using Script Task in SSIS Package - SSIS Tutorial

Scenario: Download Script

Sometime we have to read the cell value from given Excel file and then make decision either to load that file or not or perform some actions. While back, I wrote a post How to read Excel Cell Value from Excel Sheet by using Execute SQL Task, you can check the link here. The post was shared on different forums and few people had problem with reading Date type value from Excel Cell.

As I am working on Dynamic Excel Source and Destination now a days, I thought why not to write the same post by using Script Task.

In this post, we will read the cell value from Excel Sheet by using Script Task. We will insert the value into sql table so we can take a look if we like later on. Also we will save the value of cell to variable to control next tasks or use in precedence constraint or any where in our SSIS Package.

Sample Excel Files look like below

How to read Excel Cell Value in SSIS Package by using Script Task - SSIS Tutorial

Solution:

Instead of always reading single excel file. I thought I would write an SSIS Package that should read multiple files if exists and then read cell value from excel sheet.Just to notice, Script will read multiple sheets if there. It will insert the values into table but it will return you last sheet cell value for your variable to use in SSIS Package.

If you have single sheet per Excel File, you don't need to worry. If you always want to read Cell Sheet from specific Sheet, you can simply change in script, instead of using sheetname from foreach loop.

Step 1:

Create table in your database to log Cell value and Excel file with sheet name

CREATE TABLE dbo.CellInfo (
    id INT Identity(1, 1)
    ,CellValue VARCHAR(100)
    ,ExcelFileName VARCHAR(100)
    )

Step 2:

Create your SSIS Package and create below variables as shown.

CellToRead : Provide the cell which you would like to read. I am reading C4 that is date value

CellValueOutput : This is the variable that we will set the value from Script task so we can use further in other tasks/expressions in our SSIS Package. As I am making this package to read any type of Cell value. I created this variable as string. Once you have the value you can convert to date only etc.

ExcelFileFullPath : This variable is used in Foreach Loop Container to save Excel file full path.

FolderPath : This the folder path where your excel files will be dropped

LogFolder : In case of error in Script Task, error information will be written to log file and it should be in LogFolder path.

TableName : Provide the name of Table where you would like to insert Cell value and Excel File name with sheet

How to read Excel Cell Value by using Script Task in SSIS Package - SSIS Tutorial

Step 3:

Bring the For-each Loop Container and configure as shown below.

How to read Cell value from Excel Sheet in SSIS Package by using Script Task - SSIS Tutorial

Click on Variable Mapping and use ExcelFileFullPath variables as shown below and then hit ok.

Create Cell value from Excel sheet and insert into SQL table by using SSIS Package -SSIS Tutorial

Step 4:

Click in Connection Manager Pane and then Create ADO.NET Connection by providing Server Name and database Name. After creating I have renamed it to DBConn.

Create ADO.NET Connection so we can use in Script Task to load the data to Destination Table

Step 5:

Bring the Scrip Task in For-each Loop Container and then open it and add the user variables we have created above. Click on Edit Script once variables are added.

Add variables to Script Task to read Excel Cell Value in SSIS Package - SSIS Tutorial

Step 6: Add Script to Script task Editor in SSIS Package to read Excel Cell Value and Insert into SQL Server Table

Click Edit Button and it will open Script Task Editor.

Under #region Namespaces, I have added below code

using System.IO;

using System.Data.OleDb;
using System.Data.SqlClient;

Under public void Main() {

I have added below code.

            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            try
            {
                
                string CellToRead = Dts.Variables["User::CellToRead"].Value.ToString();
                string TableName = Dts.Variables["User::TableName"].Value.ToString();
                string CellName = "";
                Int32 CellNumber;
           string fileFullPath = Dts.Variables["User::ExcelFileFullPath"].Value.ToString();

var alphanumericstring = new System.Text.RegularExpressions.Regex("(?<Alpha>[a-zA-Z]*)(?<Numeric>[0-9]*)");

var match = alphanumericstring.Match(CellToRead);

                CellName = match.Groups["Alpha"].Value;
                int.TryParse(match.Groups["Numeric"].Value, out CellNumber);

                //use ADO.NET Connection created in SSIS Package
                SqlConnection myADONETConnection = new SqlConnection();
                myADONETConnection = (SqlConnection)(Dts.Connections["DB_Conn_TechBrothersIT"].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=0\"";
                OleDbConnection cnn = new OleDbConnection(ConStr);

                //MessageBox.Show(TableName);
                //Get Sheet Name
                cnn.Open();
                DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string sheetname;
                sheetname = "";
                foreach (DataRow drSheet in dtSheet.Rows)
                {
                    if (drSheet["TABLE_NAME"].ToString().Contains("$"))
                    {
                        sheetname = drSheet["TABLE_NAME"].ToString();

                        //Read the Cell value from Excel Sheet
                        OleDbCommand oconn = new OleDbCommand("select  * from [" + sheetname + CellName + (CellNumber - 1).ToString() + ":" + CellToRead + "]", cnn);
                        OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
                        DataTable dt = new DataTable();
                        adp.Fill(dt);
                        cnn.Close();

                        string CellValue = "";
                        foreach (DataRow row in dt.Rows)
                        {
                            object[] array = row.ItemArray;
                            CellValue = array[0].ToString();
                        }

                        //Insert data into table if you like
                        //Prepare Insert Query 
                        string InsertQuery = "";
                        InsertQuery = " Insert into " + TableName + "(";
                        InsertQuery += "[ExcelFileName],[CellValue])";
                        InsertQuery += "Values('ExcelFile:" + fileFullPath + "  SheetName: " + sheetname + "','" + CellValue + "')";

                        //MessageBox.Show(InsertQuery);
                        //Run Insert Query to Insert Data into SQL Server Table 
                        SqlCommand cmd = myADONETConnection.CreateCommand();
                        cmd.CommandText = InsertQuery;
                        cmd.ExecuteNonQuery();

                        //Set the value to CellValueOutput to use in SSIS Package
                        Dts.Variables["User::CellValueOutput"].Value = CellValue;


                    }
                }
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString()
                    + "\\" + "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }

            }

);

Step 7:

Save the Script and close the editor window. Bring Second Script Task so we can check if value was read correctly and set to CellValueOutput.

Inside Second Script Task , write below statement as shown, this will just print message for us with value of variable which was set in Script Task 1.

How to save Excel Cell Value to Variable by using Script Task in SSIS Package - SSIS Tutorial

use MessageBox.Show to print the value of variable that has Excel Cell value, which we saved in Script Task 1.

How to read Excel Cell Value and save to SSIS Variable in SSIS Package - SSIS Tutorial

Step 8:

Finally we are done :) close the editor windows. Run your SSIS Package. The package should read each Excel file and then read the cell value from Sheet and insert into SQL server Table and also print the value as we did by using Script Task 2.

How to read cell value from an Excel Sheet in SSIS Package by using Script Task - SSIS Tutorial

Let's check the data in the table and it should have the cell values from each excel file.

How to read Excel Cell value and write to SQL Server Table in SSIS Package - SSIS Tutorial

Video demo : How to Read Cell Value from Excel Sheet in SSIS Package by using Script Task

Check out our other posts/videos for Dynamic Excel Source and Destination

2 comments:

OlegRudenkoSeptember 25, 2023 at 1:40 PM
In the vast realm of data-driven decision-making and automation, VBA occupies a pivotal role. This platform, regarded by many as the definitive gateway to VBA excellence, combines the robust functionality of Microsoft Excel with the sheer power of programming. For those eager to unlock new horizons in data manipulation and vba code help, this is the destination. Beyond mere knowledge, the platform provides hands-on experience and expert advice, ensuring users are well-equipped to face real-world challenges.
ReplyDelete
Replies
saniyaApril 14, 2024 at 5:12 AM
"From smartphones to electric vehicles, batteries power our world." smart lithium ion battery pack
ReplyDelete
Replies

Add comment