Read Excel File in C# / SSIS script task

Today, I will discuss how to Read an Excel File.

Read from an Excel file: Follow the below steps 

1. Create a connection, OleDbConnection, to read the excel
2. Retrieve all Sheet Names into DataTable
3. Read data from a Sheet and store into DataSet
4. Read each row from DataSet and Print to Console

Add the namespace "using System.Data.OleDb;"

STEP 1. Create a connection

you have to change the Provider Microsoft.ACE.OLEDB.12.0 as per the Excel version.
change the file name and location also.
-------------------
OleDbConnection excelConnection;

string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;"
              + @"Data Source=C:\MyExcel.xlsx;"
              + "Extended Properties='Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text'";
excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
-------------------
now our Excel connection is open to read the file contants.

STEP 2:  Choose the Sheet Name to Read data.

Now we will retrieve all Sheet names into DataTable
----------------------
DataTable excelDataTable = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

foreach (DataRow row in excelDataTable.Rows)
{
string excelSheetName = row["TABLE_NAME"].ToString().Trim("'".ToCharArray());
Console.writeLine(excelSheetName);
}
----------------------

STEP 3: Read data from a Sheet and store into DataSet
by below code we will get all data of a Sheet onto excelDataSet
-------------
OleDbDataAdapter excelAdapter = new OleDbDataAdapter();
excelCommand = new OleDbCommand();
DataSet excelDataSet = new DataSet ();

excelCommand.Connection = excelConnection;
excelCommand.CommandText = "Select * from [" + excelSheetName + "]";
excelAdapter.SelectCommand = excelCommand;

excelAdapter.Fill(excelDataSet);
--------------

STEP 4: Read each row from DataSet and Print to Console

# read all data...

foreach (DataRow dr in excelDataSet.Tables[0].Rows)
{
    Console.writeLine(dr[0].ToString() + " - " + dr[1].ToString() );
}

#if you want to get actual column names of OLEDB .. try below

foreach (DataColumn dc in excelDataSet.Tables[0].Columns)
{
      Console.Write(dc.ColumnName);
}

#if the first row contains Column names then you try below code to get all values ..

foreach (object columnName in excelDataSet.Tables[0].Rows[0].ItemArray)
{
    Console.Write(columnName.ToString());
}

Executable/Tool to Send Email as HTML with Attachment & Embedded Picture

Today I am sharing a Tool/exe to send email from anywhere instantly!

always we so redundant task/ write code for the same like email sent, which is frequently used to automate a system and notify the progress... for what I have created an executable to send mail from everywhere.

Please click here to download the executable: Send_Email.zip
also, you can download the source code, click here!

Look at the below image for the options to use. YES, you can attach embedded an image with the mail!

you can get this help message by executing Send_Email.exe without any parameter

• Send_Email.exe [no parameter]

Send Email Options

Let's see how do I use this tool to send a mail. it's very easy!

The first example is to send a simple mail with an attachment

• Send_EMail.exe "m:SMTP_SERVER_ADDRESS" "to:saiful024@gmail.com" "s:testsubject" "b:Test Body" "fm:saiful.azam@bd.imshealth.com" "a:C:\TEST_ATTACHEMENT.txt"

you can write the body as HTML body

• Send_EMail.exe "m:SMTP_SERVER_ADDRESS" "to:saiful024@gmail.com" "s:testsubject" "b:<b>Test Body</b>" "fm:saiful.azam@bd.imshealth.com" "a:C:\TEST_ATTACHEMENT.txt"

also you can add pic to the mail

• Send_EMail.exe "m:MAIL_SERVER_ADDRESS" "to:saiful024@gmail.com" "s:testsubject" "b:Test <b>Body</b><br><img src=cid:IQVIA >" "fm:saiful.azam@bd.imshealth.com" "a:C:\TEST_ATTACHEMENT.txt" "p:C:\IQVIA.jpg"

NOTE: you can use "user:USER_NAME" & "pass:PASSWORD" options to provide the SMTP authentication.

• 

  Send Mail with Embedded Pic and Attachment

Send Email Command

Connect SQL Server and Execute Procedure [get data-set/rows/return value] from C#.NET

I will connect to SQL Server database and execute a procedure to get data.

a procedure can provide data by Return or as a Select statement.

I have discussed the full process step-by-step and also added the full C# code/class at the bottom of this post!

here is the DBO.Test_Proc , which i will call and get data & return value.
-------------------
Create Procedure dbo.Test_Proc  @param1 int = null, @param2 varchar = null
as
begin
     select @param1 as param1 , @param2 as param1 ;

     return isnull(@param1,-50);
end
------------------

now we have to call this procedure to get data from the select statement and from return statement as well.

step 0: use package System.Data.SqlClient = using System.Data.SqlClient;
step 1: First we have to connect to SQL server
step 2: Prepare the SQL command to call the procedure
step 3: Execute & Read data rows and return value.

STEP 1: Connect to SQL server

use below code to connect to SQL Server using SQL_Server user..
------------------
SqlConnection cnn;
string sqlConnectionString = "Data Source=SERVER_ADDRESS;Initial Catalog=" + "DB_NAME"
+ ";User ID=" + "USER_NAME" + ";Password=" + "PASSWORD";

try
{
cnn = new SqlConnection();
cnn.ConnectionString = sqlConnectionString;
cnn.Open();
Console.WriteLine("Connection Open");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
-----------------

STEP 2: SQL command to call the procedure

use below code to prepare the SQL Command to execute using above SQL Connection (cnn)
--------------------------
SqlCommand cmd;
string[] paramN = { "param1", "param2" };
string[] paramV = { "10","Name"};

cmd = new SqlCommand(sqlCommand, cnn);
cmd.CommandType = CommandType.StoredProcedure;

// add parameter to pass value to DB Proc
// 1st parameter = param1
if (!string.IsNullOrEmpty( paramV[0]))
cmd.Parameters.AddWithValue("@"+paramN[0], paramV[0]);
// 2nd parameter = param2
if (!string.IsNullOrEmpty(paramV[1]))
cmd.Parameters.AddWithValue("@" + paramN[1], paramV[1]);

// add a return type parameter [only if you required the return value from db proc]
var retParam = cmd.Parameters.Add("@retv", SqlDbType.Int);
retParam.Direction = ParameterDirection.ReturnValue;
--------------------------

STEP 3: Execute the SQL Command (cmd) and Read data rows & return value.

use below code to get data rows from proc and the return value also.
----------------------------
SqlDataReader dr;

// get data rows return by the select of proc
dr = cmd.ExecuteReader();
Console.WriteLine("executed");
while (dr.Read())
{
Console.WriteLine(dr[paramN[0]].ToString() + " -- " + dr[paramN[1]].ToString());
}
dr.close();

//// get return value by the retrun of proc
cmd.ExecuteNonQuery();
var retValue = retparam.Value;
Console.WriteLine(retValue);
---------------------------
---------------------------
OUTPUT
---------------------------

Connect SQL Server and Execute Stored Procedure

Please check below for the full C# class, i have written for me only :)

//--------------------------------------------------------------------------------\\
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

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

namespace FTP_MONITORING_EXE
{
    class FTP_MONITORING
    {
        SqlConnection cnn;
        SqlCommand cmd;
        SqlDataReader dr;

string sqlConnectionString = "Data Source=SERVER_ADDRESS;Initial Catalog=" + "DB_NAME" + ";User ID=" + "USER_NAME" + ";Password=" + "PASSWORD";

        static void Main(string[] args)
        {
            FTP_MONITORING fm = new FTP_MONITORING();

            int conS = fm.getSQLConnection();
            if (conS == 0) return;

            string[] paramV = { "10","Name"};
            string[] paramN = { "param1", "param2" };
            int retVal = fm.execProc("dbo.testProc", paramV, paramN);
            Console.WriteLine("return = " + retVal);
        }

        int execProc(string sqlCommand, string[] paramV, string[] paramN)
        {
            cmd = new SqlCommand(sqlCommand, cnn);
            cmd.CommandType = CommandType.StoredProcedure;

            for (int i = 0; i < paramN.Length; i++)
            {
                if (!string.IsNullOrEmpty(paramV[i]))
                    cmd.Parameters.AddWithValue("@" + paramN[i], paramV[i]);
            }

            SqlParameter retparam = cmd.Parameters.Add("@retv", SqlDbType.Int);
            retparam.Direction = ParameterDirection.ReturnValue;

            dr = cmd.ExecuteReader();
            Console.WriteLine("executed");
            while (dr.Read())
            {
                string output="";
                for (int i = 0; i < paramN.Length; i++)
                {
                    output = output + dr[paramN[i]].ToString() + " -- ";
                }
                Console.WriteLine(output);
            }
            dr.Close();

            cmd.ExecuteNonQuery();

            var retValue = retparam.Value;
            Console.WriteLine("retValue = " + retValue);
            cmd.Dispose();
            return (int)retValue;
        }

        int getSQLConnection()
        {
            try
            {
                cnn = new SqlConnection();
                cnn.ConnectionString = sqlConnectionString;
                cnn.Open();
                Console.WriteLine("Connection Open");
                return 1;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return 0;
            }
        }
    }
}

//----------------------------------------------------------------\\

Dynamically Create Data Warehouse (DWH) Dimension - Star Schema Dimension

Today I am sharing a procedure by which we can easily create a dimension for a data warehouse. this is very much dynamic and you know dynamic things are not so easy BUT I have tried to create something user-friendly.

first of all, we have to know about DWH dimension types. it can be FLAT or PARENT-CHILD (hierarchical).

NOTE: This Dynamic procedure is only to create PARENT-CHILD (hierarchical) dimension. if you try you can also create a part of the FLAT dimension.

FLAT Dimension: which dimension table contains flat information with some ID for main/PK column and other important columns. see below table ...

PRODUCT_ID	PRODUCT	MARKET_ID	MARKET	MANUFACTURER
P000001	PARA	M000001	A8A	ACI
P000002	NAPA	M000002	A7A	SQARE

PARENT-CHILD / Hierarchical Dimension: which dimension table contains all code in one column as PK and maintain a relationship with other attribute using parent code as a parent-child hierarchy. see below ..

PRODUCT_CODE	PARENT_CODE	DESCRIPTION	LEVEL_NO	LEVEL_NAME	MNUFACTURAR
N000001	N000001	TOTAL MARKET	1	TOTAL
M000001	N000001	A8A	2	MARKET
M000002	N000001	A7A	2	MARKET
P000001	M000001	PARA	3	PRODUCT	ACI
P000002	M000002	NAPA	3	PRODUCT	SQARE

NOTE: This Dynamic procedure is only to create PARENT-CHILD / Hierarchical dimension. if you try you can also create a part of the FLAT dimension.

All is you have to pass some parameters. A parameter can be a SQL or CASE statements.

Below is the code of this PROC -- also you can download the PROC (click here)
-----------------------------------
GLOBAL_DB.dbo.CREATE_DIM_TABLE

you can place this proc in a database and can use this from any database just make sure the use of tergat db name in table name.

@DIM_SQL	SQL for the Hierarchy and main dimension; like select level1, level2, level3…  from DB_NAME.dbo.Table_Name; Hierarchy sequence is must use full table name with db name like DB_NAME.dbo.Table_Name column name would be the LEVEL_NAME
@ATTRB_SQL	SQL for additional columns in final dimension; internal Synonym of this SQL is "B" You can say additional attributes like Manufacturer. DECSRIPTION_COLUMN is must; like below to add additional attributes only available in level3 select level3 as DESCRIPTION_COLUMN, Man from DB_NAME.dbo.Table_Name
@DIM_TABLE_NAME	Full Table Name like DB_NAME.dbo.Table_Name
@ITEM_CODE_COL_NAME	Name for Child Column which is primary key; like PRODUCT_ID
@PARENT_CODE_COL_NAME	Name for parent column to maintain relation of hierarchy
@DESCRIPTION_COL_NAME	Name for Description column, which will contain the values from Hierarchy SQL (DIM_SQL)
@LEVEL_NO_COL_NAME	Name for LEVEL Number column of Hierarchy level no
@LEVEL_NAME_COL_NAME	Name for LEVEL Name column of Hierarchy
@ITEM_CODE_RULE	you can change ITEM_CODE here using CASE, subSQL etc by using synonym "A"
@PARENT_CODE_RULE	you can change PARENT_CODE here using CASE, subSQL etc by using synonym "A"
@LEVEL_NO_RULE	you can change LEVEL_NO here using CASE, subSQL etc by using synonym "A"
@LEVEL_NAME_RULE	you can change LEVEL_NAME here using CASE, subSQL etc by using synonym "A"
@DESCRIPTION_COL_RULE	you can change Description by using Synonym "A" & "B"
@CREATE_ID_TABLE_ALSO	if you want Intermediate FLAT_DIM, set it 1 ;  internally this table has the synonym "A"
@PRINT_SQL	if you like to see all SQL generated by this proc; set it 1

Example:::

Let we have below table to create our Dimansion.

PRODUCT	ATC3	MANUFACTURER
PARA	A8A	ACI
NAPA	A7A	SQARE

now define the hierarchy..

Level1 = TOTAL MARKET
Level2 = ATC3 = MARKET
Level3 = PRODUCT

so, our output will be as below

PRODUCT_CODE	PARENT_CODE	DESCRIPTION	LEVEL_NO	LEVEL_NAME	MNUFACTURAR
T000001	T000001	TOTAL MARKET	1	TOTAL
M000001	T000001	A8A	2	MARKET
M000002	T000001	A7A	2	MARKET
P000001	M000001	PARA - ACI	3	PRODUCT	ACI
P000002	M000002	NAPA - SQARE	3	PRODUCT	SQARE

now CALL the proc to create a Table as our output !!

GLOBAL_DB.dbo.CREATE_DIM_TABLE        @DIM_SQL = 'select ''TOTAL MARKET'' as TOTAL, ATC3 as MARKET, PRODUCT as PRODUCT from TARGET_DB.dbo.TName ',
   @ATTRB_SQL = 'select PRODUCT as DESCRIPTION, MNUFACTURAR from TARGET_DB.dbo.TName ',
   @DIM_TABLE_NAME = 'TARGET_DB.dbo.PROD_DIM',
   @ITEM_CODE_COL_NAME = 'PRODUCT_CODE',
   @PARENT_CODE_COL_NAME = 'PARENT_CODE',
   @DESCRIPTION_COL_NAME = 'DESCRIPTION',
   @LEVEL_NO_COL_NAME = 'LEVEL_NO',
   @LEVEL_NAME_COL_NAME = 'LEVEL_NAME'
   @ITEM_CODE_RULE = ' case LEVEL_NO when 1 then ''T'' when 2 then ''M'' else ''P'' end + right (''0000000''+PRODUCT_CODE,8) ',
   @PARENT_CODE_RULE = ' case LEVEL_NO when 1 then ''T'' when 2 then ''T'' else ''M'' end + right (''0000000''+PARENT_CODE,8) ',
  -- @LEVEL_NO_RULE = 'NA',
  -- @LEVEL_NAME_COL_NAME = 'NA',
  @DESCRIPTION_COL_RULE = ' A.DESCRIPTION + '' - ''+B.MNUFACTURAR ',
  @CREATE_ID_TABLE_ALSO = 0,
  @PRINT_SQL = 0;
   


Below is the code of this PROC -- also you can download the PROC (click here)
---------------------------------------------

CREATE PROCEDURE [DBO].[CREATE_DIM_TABLE] @DIM_SQL VARCHAR(MAX)
 , @ATTRB_SQL VARCHAR(MAX) = 'NA'
 , @DIM_TABLE_NAME VARCHAR(1000)
 , @ITEM_CODE_COL_NAME VARCHAR(1000) = 'ITEM_CODE'
 , @PARENT_CODE_COL_NAME VARCHAR(100) = 'PARENT_CODE'
 , @DESCRIPTION_COL_NAME VARCHAR(100) = 'DESCRIPTION_COLUMN'
 , @LEVEL_NO_COL_NAME VARCHAR(100) = 'LEVEL_NUMBER'
 , @LEVEL_NAME_COL_NAME VARCHAR(100) = 'LEVEL_NAME'
 , @ITEM_CODE_RULE VARCHAR(1000) = 'NA'
 , @PARENT_CODE_RULE VARCHAR(1000) = 'NA'
 , @LEVEL_NO_RULE VARCHAR(1000) = 'NA'
 , @LEVEL_NAME_RULE VARCHAR(1000) = 'NA'
 , @DESCRIPTION_COL_RULE VARCHAR(1000) = 'NA'
 , @CREATE_ID_TABLE_ALSO BIT = 0
 , @PRINT_SQL BIT = 0
AS
BEGIN
 DECLARE @DB_NAME VARCHAR(100) = isnull(PARSENAME(@DIM_TABLE_NAME, 3), DB_NAME());
 DECLARE @Drop_SQL NVARCHAR(2000);
 SET NOCOUNT ON
 DECLARE @isTable BIT = 'False'

 IF (
   @ITEM_CODE_RULE = 'NA'
   OR @ITEM_CODE_RULE = ''
   )
  SET @ITEM_CODE_RULE = @ITEM_CODE_COL_NAME

 IF (
   @PARENT_CODE_RULE = 'NA'
   OR @PARENT_CODE_RULE = ''
   )
  SET @PARENT_CODE_RULE = @PARENT_CODE_COL_NAME

 IF (
   @LEVEL_NAME_RULE = 'NA'
   OR @LEVEL_NAME_RULE = ''
   )
  SET @LEVEL_NAME_RULE = @LEVEL_NAME_COL_NAME 

IF (
   @LEVEL_NO_RULE = 'NA'
   OR @LEVEL_NO_RULE = ''
   )
  SET @LEVEL_NO_RULE = @LEVEL_NO_COL_NAME 

 IF (
   @DESCRIPTION_COL_RULE = 'NA'
   OR @DESCRIPTION_COL_RULE = ''
   )
  SET @DESCRIPTION_COL_RULE = 'a.' + @DESCRIPTION_COL_NAME

 IF (@ATTRB_SQL = '')
  SET @ATTRB_SQL = 'NA'

 DECLARE @metaDataTableSQL VARCHAR(MAX) = 'select * into ##tmpMataDataScript from ' + CASE @isTable
   WHEN 'True'
    THEN @DIM_SQL
   ELSE ' ( ' + @DIM_SQL + ' )'
   END + ' a'
 DECLARE @mataDataSQL VARCHAR(MAX) = 'select * from ##tmpMataDataScript'

 IF object_id('tempdb..##tmpMataDataScript') IS NOT NULL
 BEGIN
  DROP TABLE ##tmpMataDataScript
 END

 DECLARE @Columns TABLE (
  TABLE_QUALIFIER VARCHAR(500)
  , TABLE_OWNER VARCHAR(500)
  , TABLE_NAME VARCHAR(500)
  , COLUMN_NAME VARCHAR(500)
  , DATA_TYPE VARCHAR(500)
  , TYPE_NAME VARCHAR(500)
  , PRECISION VARCHAR(500)
  , LENGTH VARCHAR(500)
  , SCALE VARCHAR(500)
  , RADIX VARCHAR(500)
  , NULLABLE VARCHAR(500)
  , REMARKS VARCHAR(500)
  , COLUMN_DEF VARCHAR(500)
  , SQL_DATA_TYPE VARCHAR(500)
  , SQL_DATETIME_SUB VARCHAR(500)
  , CHAR_OCTET_LENGTH VARCHAR(500)
  , ORDINAL_POSITION VARCHAR(500)
  , IS_NULLABLE VARCHAR(500)
  , SS_DATA_TYPE VARCHAR(500)
  )

 EXEC (@metaDataTableSQL)

 INSERT INTO @Columns
 EXEC tempdb..sp_columns @table_name = N'##tmpMataDataScript'

 UPDATE @Columns
 SET COLUMN_NAME = QUOTENAME(COLUMN_NAME)

 DECLARE @Table_ID TABLE (
  ORDINAL_POSITION INT
  , COLUMN_NAME VARCHAR(1000)
  , COLUMN_GROUP VARCHAR(1000)
  , CONDITION VARCHAR(5000)
  , LEFT_COLUMN_NAME AS ('a' + cast(ORDINAL_POSITION AS VARCHAR) + '.' + QUOTENAME('ia' + cast(ORDINAL_POSITION AS VARCHAR) + '_' + COLUMN_NAME + '_code')
   )
  , LEFT_JOIN AS (
   ' LEFT JOIN (
 SELECT cast(ROW_NUMBER() OVER (
    ORDER BY ' + COLUMN_GROUP + ') AS VARCHAR) ' + QUOTENAME('ia' + cast(ORDINAL_POSITION AS 
     VARCHAR) + '_' + COLUMN_NAME + '_code ') + ',' + COLUMN_GROUP + ' from Data ia' + cast(ORDINAL_POSITION AS VARCHAR) + ' group by ' + COLUMN_GROUP + 
   ' ) ' + 'a' + cast(ORDINAL_POSITION AS VARCHAR) + ' ON ' + CONDITION
   )
  , UNION_ALL AS (
   'SELECT ' + QUOTENAME(COLUMN_NAME + '_CODE') + ' AS @ITEM_CODE_COL_NAME, ' + QUOTENAME('LEVEL' + cast((ORDINAL_POSITION - 1) AS 
     VARCHAR) + '_CODE') + ' as @PARENT_CODE_COL_NAME, ' + COLUMN_NAME + ' AS @DESCRIPTION_COL_NAME, ''' + cast(ORDINAL_POSITION AS VARCHAR) + '''' + 
   ' AS @LEVEL_NO_COL_NAME, ' + '''' + replace(replace(COLUMN_NAME, '[', ''), ']', '') + '''' + ' AS @LEVEL_NAME_COL_NAME ' + ' from ' + '@DIM_TABLE_NAME' + 
   ' GROUP BY ' + QUOTENAME('LEVEL' + cast((ORDINAL_POSITION - 1) AS VARCHAR) + '_CODE') + ' , ' + QUOTENAME(COLUMN_NAME + '_CODE') + 
   ',' + COLUMN_NAME
   )
  )

 INSERT INTO @Table_ID (
  ORDINAL_POSITION
  , COLUMN_NAME
  )
 SELECT ORDINAL_POSITION
  , COLUMN_NAME
 FROM @Columns
 ORDER BY ORDINAL_POSITION

 UPDATE @Table_ID
 SET COLUMN_GROUP = substring((
    SELECT ',' + COLUMN_NAME
    FROM @Table_ID b
    WHERE a.ORDINAL_POSITION >= b.ORDINAL_POSITION
    FOR XML path('')
    ), 2, 50000)
  , CONDITION = substring((
    SELECT ' AND  a.' + COLUMN_NAME + '=' + 'a' + cast(ORDINAL_POSITION AS VARCHAR) + '.' + COLUMN_NAME
    FROM @Table_ID b
    WHERE a.ORDINAL_POSITION >= b.ORDINAL_POSITION
    FOR XML path('')
    ), 5, 50000)
 FROM @Table_ID a

 DECLARE @FINAL_QUERY VARCHAR(MAX) = 'WITH Data as (' + @DIM_SQL + ') select '
 DECLARE @left_join VARCHAR(MAX) = ''
 DECLARE @FINAL_QUERY_SELECT VARCHAR(MAX) = ''

 SELECT @FINAL_QUERY_SELECT = @FINAL_QUERY_SELECT + ', a.' + a.COLUMN_NAME + ', ' + a.LEFT_COLUMN_NAME + ' AS ' + QUOTENAME(a.COLUMN_NAME + '_CODE')
  , @left_join = @left_join + a.LEFT_JOIN
 FROM @Table_ID a

 SET @FINAL_QUERY = @FINAL_QUERY + substring(@FINAL_QUERY_SELECT, 2, 50000) + ' INTO ' + @DIM_TABLE_NAME + '_ID' + ' from Data a ' + @left_join
 
 SET @Drop_SQL = 'IF EXISTS (
   SELECT *
   FROM ' + @DB_NAME + '.sys.objects
   WHERE object_id = OBJECT_ID(''' + @DIM_TABLE_NAME + '' + 
  '_ID'')
    AND type IN (N''U'')
   )
  EXEC (''DROP TABLE ' + @DIM_TABLE_NAME + '' + '_ID'')';

 EXEC (@Drop_SQL)

 IF @PRINT_SQL = 1
  PRINT @FINAL_QUERY

 EXEC (@FINAL_QUERY)

 SET @FINAL_QUERY_SELECT = ''

 SELECT @FINAL_QUERY_SELECT = @FINAL_QUERY_SELECT + ' UNION ALL ' + a.UNION_ALL
 FROM @Table_ID a

 SET @FINAL_QUERY_SELECT = SUBSTRING(@FINAL_QUERY_SELECT, 12, 50000)

 SELECT @FINAL_QUERY_SELECT = CASE 
   WHEN a.ORDINAL_POSITION = 1
    THEN replace(replace(@FINAL_QUERY_SELECT, 'LEVEL0', a.COLUMN_NAME + ']'), 'LEVEL1', a.COLUMN_NAME + ']')
   ELSE replace(@FINAL_QUERY_SELECT, 'LEVEL' + cast(ORDINAL_POSITION AS VARCHAR), a.COLUMN_NAME + ']')
   END
 FROM @Table_ID a

 DECLARE @DIM_TABLE_NAME_INT VARCHAR(1050) = @DIM_TABLE_NAME + CASE @ATTRB_SQL
   WHEN 'NA'
    THEN ''
   ELSE '_INT'
   END

 SET @FINAL_QUERY_SELECT = 
  'select @ITEM_CODE_RULE as @ITEM_CODE_COL_NAME,@PARENT_CODE_RULE as @PARENT_CODE_COL_NAME,@DESCRIPTION_COL_NAME,@LEVEL_NO_RULE as @LEVEL_NO_COL_NAME,@LEVEL_NAME_RULE as @LEVEL_NAME_COL_NAME INTO ' 
  + @DIM_TABLE_NAME_INT + ' from ( ' + replace(@FINAL_QUERY_SELECT, '@DIM_TABLE_NAME', @DIM_TABLE_NAME + '_ID') + 
  ' ) a ORDER BY cast (@LEVEL_NO_COL_NAME as int),' + CASE rtrim(LTRIM(@ITEM_CODE_RULE))
   WHEN rtrim(LTRIM(@PARENT_CODE_RULE))
    THEN ''
   ELSE CASE 
     WHEN @PARENT_CODE_RULE = @PARENT_CODE_COL_NAME
      THEN 'cast(@PARENT_CODE_RULE as int), '
     ELSE '@PARENT_CODE_RULE, '
     END
   END + CASE 
   WHEN @ITEM_CODE_RULE = @ITEM_CODE_COL_NAME
    THEN ' cast(@ITEM_CODE_RULE as int)'
   ELSE ' @ITEM_CODE_RULE'
   END

 SET @Drop_SQL = 'IF EXISTS (
   SELECT *
   FROM ' + @DB_NAME + '.sys.objects
   WHERE object_id = OBJECT_ID(''' + @DIM_TABLE_NAME_INT + 
  ''')
    AND type IN (N''U'')
   )
  EXEC (''DROP TABLE ' + @DIM_TABLE_NAME_INT + ''')';

 EXEC (@Drop_SQL)

 SET @FINAL_QUERY_SELECT = REPLACE(@FINAL_QUERY_SELECT, '@ITEM_CODE_COL_NAME', @ITEM_CODE_COL_NAME)
 SET @FINAL_QUERY_SELECT = replace(@FINAL_QUERY_SELECT, '@PARENT_CODE_COL_NAME', @PARENT_CODE_COL_NAME)
 SET @FINAL_QUERY_SELECT = replace(@FINAL_QUERY_SELECT, '@DESCRIPTION_COL_NAME', @DESCRIPTION_COL_NAME)
 SET @FINAL_QUERY_SELECT = replace(@FINAL_QUERY_SELECT, '@LEVEL_NO_COL_NAME', @LEVEL_NO_COL_NAME)
 SET @FINAL_QUERY_SELECT = replace(@FINAL_QUERY_SELECT, '@LEVEL_NAME_COL_NAME', @LEVEL_NAME_COL_NAME)
 SET @FINAL_QUERY_SELECT = replace(@FINAL_QUERY_SELECT, '@ITEM_CODE_RULE', @ITEM_CODE_RULE)
 SET @FINAL_QUERY_SELECT = replace(@FINAL_QUERY_SELECT, '@PARENT_CODE_RULE', @PARENT_CODE_RULE)
 SET @FINAL_QUERY_SELECT = replace(@FINAL_QUERY_SELECT, '@LEVEL_NO_RULE', @LEVEL_NO_RULE)
 SET @FINAL_QUERY_SELECT = replace(@FINAL_QUERY_SELECT, '@LEVEL_NAME_RULE', @LEVEL_NAME_RULE)

 IF @PRINT_SQL = 1
  PRINT @FINAL_QUERY_SELECT

 EXEC (@FINAL_QUERY_SELECT)

 SET @Drop_SQL = 'IF EXISTS (
   SELECT *
   FROM ' + @DB_NAME + '.sys.objects
   WHERE object_id = OBJECT_ID(''' + @DIM_TABLE_NAME + '' + 
  '_ID'')
    AND type IN (N''U'')
   )
   AND ' + cast(@CREATE_ID_TABLE_ALSO AS VARCHAR) + ' = 0
  EXEC (''DROP TABLE ' + @DIM_TABLE_NAME + '' + 
  '_ID'')';

 EXEC (@Drop_SQL)

 IF @DIM_TABLE_NAME <> @DIM_TABLE_NAME_INT
 BEGIN
  SET @metaDataTableSQL = 'select * into ##tmpMataDataScript from ' + ' ( ' + @ATTRB_SQL + ' )' + ' a'
  SET @mataDataSQL = 'select * from ##tmpMataDataScript'

  IF object_id('tempdb..##tmpMataDataScript') IS NOT NULL
  BEGIN
   DROP TABLE ##tmpMataDataScript
  END
 
  DELETE
  FROM @Columns

  EXEC (@metaDataTableSQL)

  INSERT INTO @Columns
  EXEC tempdb..sp_columns @table_name = N'##tmpMataDataScript'

  UPDATE @Columns
  SET COLUMN_NAME = QUOTENAME(COLUMN_NAME)

  SET @FINAL_QUERY_SELECT = 'select ' + 'a.' + @ITEM_CODE_COL_NAME + ', a.' + @PARENT_CODE_COL_NAME + ',' + @DESCRIPTION_COL_RULE + ' as ' + @DESCRIPTION_COL_NAME + 
   ', a.' + @LEVEL_NO_COL_NAME + ', a.' + @LEVEL_NAME_COL_NAME

  SELECT @FINAL_QUERY_SELECT = @FINAL_QUERY_SELECT + ', b.' + a.COLUMN_NAME
  FROM @Columns a
  WHERE a.COLUMN_NAME NOT LIKE '%' + @DESCRIPTION_COL_NAME + '%'
  ORDER BY a.ORDINAL_POSITION

  SET @FINAL_QUERY_SELECT = @FINAL_QUERY_SELECT + ' into ' + @DIM_TABLE_NAME + ' from ' + @DIM_TABLE_NAME_INT + ' a ' + ' left join (' + @ATTRB_SQL + ') b on a.' + 
   @DESCRIPTION_COL_NAME + ' = b.' + @DESCRIPTION_COL_NAME + ' order by cast(a.' + @LEVEL_NO_COL_NAME + ' as int) , ' + CASE 
    WHEN @PARENT_CODE_RULE = @PARENT_CODE_COL_NAME
     THEN 'cast(' + 'a.' + @PARENT_CODE_COL_NAME + ' as int), '
    ELSE 'a.' + @PARENT_CODE_COL_NAME + ' ,'
    END + CASE 
    WHEN @ITEM_CODE_RULE = @ITEM_CODE_COL_NAME
     THEN 'cast(' + 'a.' + @ITEM_CODE_COL_NAME + ' as int) '
    ELSE 'a.' + @ITEM_CODE_COL_NAME
    END

  IF @PRINT_SQL = 1
   PRINT (@FINAL_QUERY_SELECT)

  SET @Drop_SQL = 'IF EXISTS (
   SELECT *
   FROM ' + @DB_NAME + '.sys.objects
   WHERE object_id = OBJECT_ID(''' + @DIM_TABLE_NAME + 
   ''')
    AND type IN (N''U'')
   )
  EXEC (''DROP TABLE ' + @DIM_TABLE_NAME + ''')';

  EXEC (@Drop_SQL)

  EXEC (@FINAL_QUERY_SELECT)

  SET @Drop_SQL = 'IF EXISTS (
   SELECT *
   FROM ' + @DB_NAME + '.sys.objects
   WHERE object_id = OBJECT_ID(''' + @DIM_TABLE_NAME_INT + 
   ''')
    AND type IN (N''U'')
   )
  EXEC (''DROP TABLE ' + @DIM_TABLE_NAME_INT + ''')';

  EXEC (@Drop_SQL)
 END
END