Implementation of slowly changing Dimension Type-0 The Passive method (Ignore updates) using SCD Transformation in SSIS

We can implement the SCD type -0 using the SCD transformation.

Read: Implementation of slowly changing Dimension Type-0 ThePassive method (Ignore updates) using SQL script in SQL server

https://bageshkumarbagi-msbi.blogspot.com/2022/05/implementation-of-slowly-changing.html

Taking Data flow task 

Taking Source as OLEDB and doing the configuration.  

Now we are taking  SCD transformation and clicking  

Providing the connection manager and selecting the destination table. After that we are selecting the Business key. Business key is nothing but on which condition we are going the update.

Click on the next button.  

Selecting the Dimension columns and Change Type as Fixed attribute. Click on the Next button.

  

If we check mark this check box then if we get any change record in PAN or Aadhar Number it will fail the package. If Uncheck this box then we can either ignore that record or we can Audit that record.

Here we are auditing the record.

  Click on Next.  

Here we are not enabling the inferred member support.

Click on the next button. 

Click on the Finish.  

Now we can add one more destination to audit in case if there is any changes in the dimension attribute.

                          
Basically in the derived column transformation we are creating two new columns one is for application name and column for audit message.  

After that we are taking a OLEDB destination and doing the mapping.  

Our package looks like below  

Before running the package records in the table.  

Running the package now.  

Package is executed successfully.

See the records in the table. 

Get the expected result.

ODBC Destination in Data Flow Task in SSIS

The ODBC destination is used to bulk or row by row data load into the ODBC Supported database tables. It uses ODBC connection manager to connect the destination.

For the ODBC destination we need to create ODBC connection manager.

Read here Step by step how to create the OBDC connection manager:

https://bageshkumarbagi-msbi.blogspot.com/2022/01/creating-odbc-connection-for-postgresql.html

Generally we are not able to find out ODBC Destination on the Other Destination pan.

  

 We will see it into the Common  

Taking ODBC Destination and configuring it using ODBC connection manager.

Some important property of ODBC Source

ODBC Connection Manager: here we will create or add the ODBC connection Manager.

Data access mode:

Table Name – row by row: if we select this option record are inserted one row at a time.

Table Name – Batch: If we select Batch we need to provide the batch size to insert records in to the destination table. 

If we select batch and provider doesn’t support this method at the ODBC destination in this case automatically switches to Row by row mode.

Other important properties we can see in the Show Advance tab. 

Batch Size:  Here we can give the size of the batch for the bulk extraction. If selected ODBC provider does not support the array in that case batch size will by 1.

Insert Method:   it will be 0 if we select Data access mode as table name – row by row and 1 if table name – batch.

LobChunksize :  Determines the chunk size allocation for LOB column. By default value of this property is 32768.

StatementTimeout :  Number of seconds to wait for an SQL statement to execute before returning to the application. The default value is 0. A value of 0 indicates that the system does not time out.

TransactionSize : In this property we can set the transaction size(Number of rows inserted under the same transaction).

ODBC Source in Data Flow Task in SSIS

ODBC Source is used to extract the data from the ODBC supported database. Like Postgre database or Oracle database. We can use table, view or SQL statement to extract the data from the database.

In the ODBC source either we can extract the data Row-by-Row or Batch mode.

Below is the ODBC Source

For the ODBC Source we need to create ODBC connection manager.

Read here Step by step how to create the OBDC connection manager:

https://bageshkumarbagi-msbi.blogspot.com/2022/01/creating-odbc-connection-for-postgresql.html

Creating the ODBC Connection Manager and using this connection manager in ODBC Source. 

                 

Some important property of ODBC Source

ODBC Connection Manager: here we will create or add the ODBC connection Manager.

Data access Mode:

Table Name: Here we are selecting the table or view name from where we want to pull the data. When we will click on the Name of the table or the view we will able to see the view name or table name. If our database having more than 1000 table in that case we may not able to see the table or view name in this case we can write the table name.

SQL command: Here we need to write the SQL script, or function or SP to pull the records from the source.

Some other we can see in the advance editor.  

Right click on the task we will see the Show Advance Editor.  

Batch Size:  Here we can give the size of the batch for the bulk extraction. If selected ODBC provider does not support the array in that case batch size will by 1.

LobChunksize :  Determines the chunk size allocation for LOB column. By default value of this property is 32768.

StatementTimeout :  Number of seconds to wait for an SQL statement to execute before returning to the application. The default value is 0. A value of 0 indicates that the system does not time out.

Load (merge) all CSV files data into a single CSV file

We are getting n different CSV files with the same structure. Our requirement is to merge the all the CSV files into the one file.

Below are the files

We need to merge all these files into the single CSV file.

We are taking the Script task to merge theses files into the single CSV file.

 

Taking the below variables.

 

Now in script task we are writing the below code.

public void Main()                                 {                        string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");             try             {                 string Scr_Files = Dts.Variables["User::Scr_Files"].Value.ToString();                 string Marge_File_Nm = Dts.Variables["User::Desc_File"].Value.ToString() + "\\" + "Marged_csv_File" + "_" + datetime + ".csv";                   int counter = 0;                 string[] fileEntries = Directory.GetFiles(Scr_Files);                 foreach (string fileName in fileEntries)                 {                        string line;                        System.IO.StreamReader SourceFile = new System.IO.StreamReader(fileName);                        StreamWriter sw = null;                     sw = new StreamWriter(Marge_File_Nm, true);                        int linecnt = 0;                     while ((line = SourceFile.ReadLine()) != null)                     {                         //Write only the header from first file                         if (counter == 0 && linecnt == 0)                         {                             sw.Write(line);                             sw.Write(sw.NewLine);                          }                         //Write data records from flat files                         if (linecnt != 0)                         {                             sw.Write(line);                             sw.Write(sw.NewLine);                          }                         linecnt++;                         counter++;                     }                        sw.Close();                     Dts.TaskResult = (int)ScriptResults.Success;                 }                 Dts.TaskResult = (int)ScriptResults.Success;              }              catch (Exception ex)             {             Dts.Events.FireError(0, "Fire Error", "An error occurred: " + ex.Message.ToString(), "", 0);             Dts.TaskResult = (int)ScriptResults.Failure;             }                                           }

Now our package is ready to run.

Below is the destination

  

Now we are running this package.

  

See the destination folder.

 

We get the expected result.

Extract date and Row count from a fix length flat file

We are getting the fix length flat file which having the Header & Trailer row. Header Row is having the Date and the Trailer Row is having the Rows counts. Date and Row Counts are in the fix place.

Below is the file     

Date is staring from (22,8) and row count is staring from (42,5).

We are taking the script task to read the Header & Trailer record and from both line we are subtracting the date and count.

Taking below variables to store the file_path,Row_count and File_date.  

Passing these value in script task.

 

In script task we are writing the below code.

public void Main()                                 {             // TODO: Add your code here             //User::File_date,User::File_path,User::Row_count             string header = "HEADER";             string trailer = "TRAILER";                     string textFile = Dts.Variables["User::File_path"].Value.ToString();             string[] lines = File.ReadAllLines(@textFile);             foreach (string line in lines)             {                 if (line.Contains(header))                 {                     Dts.Variables["User::File_date"].Value= line.Substring(22, 8);                                  }                 if (line.Contains(trailer))                 {                     Dts.Variables["User::Row_count"].Value = line.Substring(42, 5);                                     }             }             Dts.TaskResult = (int)ScriptResults.Success;                                 }

Now we can use this variable where we need.

We are taking one more script task to see the value.

 

Below is the code.

public void Main()                                 {             // TODO: Add your code here             MessageBox.Show("File date : "+Dts.Variables["User::File_date"].Value.ToString()+" Number of rows : "+Dts.Variables["User::Row_count"].Value.ToString());             Dts.TaskResult = (int)ScriptResults.Success;                                 }

Our package looks like as below.

 

Now running this package.

 

Get the expected result.

Thanks for reading J

Creating ODBC Connection for the Postgresql Database

When we are creating the DSN for ODBC connection to connect the Postgresql Database             first time we need to check that psqlodbc driver install in our system or not.  Without this driver we can’t create the DSN.

First we need to install the Driver. Below is the official website to download the driver.

https://www.postgresql.org/ftp/odbc/versions/msi/                

  

After Download we need to install this driver.

Let’s create the DSN

Open the ODBC data Source              

                           

In User DSN click on the Add button.  

We need to select Postgresql Unicode data Source. Click on Finish button.

 

In this Window we need to provide the all required information.

Click on the Test button to validate the connection.

Click ok. Save this window.

  

DSN is created successfully.

Send the n Different type of mail based on the product category name to respective product sellers

We have a requirement that we need to send the sales summary of the product on the respective sellers.

For the demo we are using the AdventureWorksDW Database.

We have product sales information.

SELECT pc.productcategorykey,        pc.englishproductcategoryname AS Productcategoryname,        Sum(f.unitprice)              AS TotalUnitPrice,        Sum(f.extendedamount)         AS TotalExtendedAmount,        Sum(f.unitpricediscountpct)   AS TotalUnitPriceDiscountPct,        Sum(f.discountamount)         AS TotalDiscountAmount,        Sum(f.productstandardcost)    AS TotalProductStandardCost,        Sum(f.totalproductcost)       AS TotalProductCost,        Sum(f.taxamt)                 AS TotalTaxAmt,        Sum(f.freight)                AS TotalFreight,        Sum(f.salesamount)            AS Totalsales FROM   dimproductcategory pc        INNER JOIN dimproductsubcategory Psc                ON PC.productcategorykey = psc.productcategorykey        INNER JOIN dimproduct p                ON p.productsubcategorykey = pc.productcategorykey        INNER JOIN factinternetsales f                ON f.productkey = p.productkey GROUP  BY pc.productcategorykey,           pc.englishproductcategoryname

   

We need to send the mail to respective user.   

We have user table.  

We are mapping the product category key with user id and sending that sales summary.

Mail Subject is :  Product name sales Report .

In mail greetings

·         If there is one email id then

o   Hello user name

·         If there are two email id then

o   Hello User name1 & User name2

·         If more than 2 then

o   Hello Team

We are writing the query to get the email id with the product sales summery details.

SELECT Product_details.productcategorykey,        Product_details.productcategoryname,        Product_details.totalunitprice,        Product_details.totalextendedamount,        Product_details.totalunitpricediscountpct,        Product_details.totaldiscountamount,        Product_details.totalproductstandardcost,        Product_details.totalproductcost,        Product_details.totaltaxamt,        Product_details.totalfreight,        Product_details.totalsales,        email.username,        email.useremailid FROM   (SELECT pc.productcategorykey,                pc.englishproductcategoryname AS Productcategoryname,                Sum(f.unitprice)              AS TotalUnitPrice,                Sum(f.extendedamount)         AS TotalExtendedAmount,                Sum(f.unitpricediscountpct)   AS TotalUnitPriceDiscountPct,                Sum(f.discountamount)         AS TotalDiscountAmount,                Sum(f.productstandardcost)    AS TotalProductStandardCost,                Sum(f.totalproductcost)       AS TotalProductCost,                Sum(f.taxamt)                 AS TotalTaxAmt,                Sum(f.freight)                AS TotalFreight,                Sum(f.salesamount)            AS Totalsales         FROM   dimproductcategory pc                INNER JOIN dimproductsubcategory Psc                        ON PC.productcategorykey = psc.productcategorykey                INNER JOIN dimproduct p                        ON p.productsubcategorykey = pc.productcategorykey                INNER JOIN factinternetsales f                        ON f.productkey = p.productkey         GROUP  BY pc.productcategorykey,                   pc.englishproductcategoryname) Product_details        LEFT JOIN (SELECT t1.productcategorykey               AS                          ProductCategoryKey,                          Stuff((SELECT ',' + t.username                                 FROM   usertbl AS t                                 WHERE  t.productcategorykey                                        = t1.productcategorykey                                 ORDER  BY t.productcategorykey                                 FOR xml path('')), 1, 1, '') AS Username,                          Stuff((SELECT ',' + t.useremailid                                 FROM   usertbl AS t                                 WHERE  t.productcategorykey                                        = t1.productcategorykey                                 ORDER  BY t.productcategorykey                                 FOR xml path('')), 1, 1, '') AS Useremailid                   FROM   usertbl t1                   GROUP  BY t1.productcategorykey) email               ON email.productcategorykey = Product_details.productcategorykey

  

Now we are designing the package.

Taking Execute SQL Task and running the above script and storing this result in the object variable.

  

Storing this result in the object variable.

   

Now taking the Script task to send the mail.

  

We are wring the below C# script to send the mail.

public void Main()                                 {                        DataTable dt = new DataTable();             var oleDa = new OleDbDataAdapter();             oleDa.Fill(dt, Dts.Variables["User::ProductDeatils"].Value);             string smtpAddress = "smtp.gmail.com";             int portNumber = 587;             bool enableSSL = true;             string emailFromAddress = "***********@gmail.com"; //Sender Email Address             string password = "*********"; //Sender Password                      foreach (DataRow row in dt.Rows)             {                 try                 {                                        string emailToAddress = row.ItemArray[12].ToString(); //Receiver Email Address                     string subject = row.ItemArray[1].ToString()+ " Sales Report";                     string body = " ";                     body = "Hello ";                     string[] words = row.ItemArray[11].ToString().Split(',');                     int i = words.Length;                     if (i == 1)                     {                         body = body + row.ItemArray[11].ToString()+",";                     }                     else if (i == 2)                     {                           body = body + words[0].ToString() + " and " + words[1].ToString() + ",";                     }                     else                     {                         body = body + " Team,";                     }                       body = body + Environment.NewLine +Environment.NewLine+ row.ItemArray[1].ToString() + " Report deatil as below.";                                                    using (MailMessage mail = new MailMessage())                     {                         mail.From = new MailAddress(emailFromAddress);                         mail.To.Add(emailToAddress);                         mail.Subject = subject;                         mail.Body = body;                                                                      mail.IsBodyHtml = true;                         mail.Body += " <html>";                         mail.Body += "<body>";                         mail.Body += "<table border=2>";                         mail.Body += "<tr>";                         mail.Body += "<td bgcolor=#00FFFF>Product category name</td><td bgcolor=#00FFFF>Total Unit Price</td>"+                             "<td bgcolor =#00FFFF>Total Extended Amount</td><td bgcolor=#00FFFF>Total Unit Price Discount Pct</td>" +                             "<td bgcolor =#00FFFF>Total Discount Amount</td><td bgcolor=#00FFFF>Total Product Standard Cost</td>" +                             "<td bgcolor =#00FFFF>Total Product Cost</td><td bgcolor=#00FFFF>Total TaxAmt</td>"+                             "<td bgcolor =#00FFFF>Total Freight</td><td bgcolor=#00FFFF>Tota lsales</td>";                             mail.Body += "</tr>";                                                mail.Body += "<tr>";                         mail.Body += "<td>" + row.ItemArray[1].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[2].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[3].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[4].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[5].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[6].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[7].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[8].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[9].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[10].ToString() + "</td>";                           mail.Body += "</tr>";                                               mail.Body += "</table>";                         mail.Body += "<br/><br/>";                         mail.Body += "Thank and Regards,<br/>";                         mail.Body += "DWH Load Team <br/>";                         mail.Body += "XYZ</body>";                         mail.Body += "</html>";                           using (SmtpClient smtp = new SmtpClient(smtpAddress, portNumber))                         {                             smtp.Credentials = new NetworkCredential(emailFromAddress, password);                             smtp.EnableSsl = enableSSL;                             smtp.Send(mail);                         }                     }                 }                   catch (Exception ex)                 {              Dts.Events.FireError(0, "Fire Error", "An error occurred: " + ex.Message.ToString(), "", 0);                     Dts.TaskResult = (int)ScriptResults.Failure;                 }               }             Dts.TaskResult = (int)ScriptResults.Success; }

 Now Package is ready to run.   

Running the package now.

Clothing Sales Report. 

Other user mail box   

Other user mail 

Other user 

Get the expected result.