Bro and Sis Math Club

Bro and Sis Math Club is an idea of two 7th Graders ( Najaf and Rida ) to learn and teach Math by using technologies available to them so the kids around the globe can learn with them.

They have the blog posts as well  Youtube videos available for the kids to follow step by step and learn Math Skills.


Website Link www.broandsismathclub.com

YouTube Channel : Najaf Rida YouTube Channel

Good Plus Community : Bro and Sis Math Club Google Community

FaceBook: Bro and Sis Math Club Facebook Page

SSIS - How to Create Multiple Files Dynamically From a SQL Server Table

Scenario:

Let's say we have data sale data in dbo.CustomerData table for different countries. We are asked to generate file for each of the country. The file should have data only related to that country and File Name should be CountryName.text. 

Solution:

We will create SSIS Package to perform this,Let's proceed with step by step approach.

Step 1: 

Create dbo.CustomerData with some data so we can use as our source by using below script.
USE TestDB
GO
CREATE TABLE dbo.CustomerData
  (
     Customerid   INT,
     CustomerName VARCHAR(100),
     Sale         INT,
     CountryName  VARCHAR(100)
  )
GO
INSERT INTO dbo.CustomerData
VALUES     ( 1,
             'Aamir',
             100,
             'USA'),
            (2,
             'Raza',
             400,
             'USA'),
            (3,
             'Singh',
             1000,
             'India'),
            (4,
             'Robert',
             500,
             'Germany') 

Step 2: 

Create SQL Server Integration Services Project and add new SSIS Package to it. 

Step 3:

Create two variables 
CountryNameObj: Object Type variable to hold all the distinct Countries
CountryName: We will use this variable to hold the country name inside For-each loop.
Fig 1: Package Variables

Step 4:

Bring Execute SQL Task to Control Flow Surface and configure as shown below. We are getting distinct countries from table so we can use them inside For-Each loop to create files.
Fig 2:Populate CountryNameObj variable by using Full Result Set in Execute SQL Task

Fig 3: Map the Result Set to CountryNameObj variable

Step 5:

Bring For-Each Loop and connect the Execute SQL Task to it and configure as shown below. We will be loop through the list of Countries (CountryNameObj variable) and then use one country at a time to get data from table and create file.
Fig 4: For-Each Loop read Country List from CountryNameObj Variable

Fig 5: Map the value to CountryName variable


Step 6:

Bring the Data Flow Task and put inside For-Each Loop Container. Inside Data Flow Task, Drag OLE DB Source and configure as shown below
This is the query I used 

Select Customerid,CustomerName,Sale,CountryName from dbo.CustomerData
WHERE CountryName=?
Fig 6:OLE DB Source to use Command with Parameter

Fig 7: Map the CountryName variable to the Query inside OLE DB Source

Step 7: 

Drag Flat File Destination and Connect OLE DB Source to it.Double click on Flat File Destination and create connection. you can create any name you like, you can have .csv or text file that is depending upon your requirement. The name will be overwritten while we will execute the package by expression that we are going to use in next step.
Fig 8: Create Flat File Connection to Test File 

Step 8: 

As we want to create file with Country Name, we need to write expression on Flat File Destination Connection Manager. Right Click on Flat File Connection Manager and go to properties. Then Click on Expressions and choose the Connection String as shown in fig
Fig 9: Write Expression on Flat File Connection Manager to generate file name by using Country Name variable

Write below expressions as shown, Change the Folder Path according to your Folder Path.
"C:\\Users\\ashahzad\\Desktop\\"+ @[User::CountryName]+".txt"
Fig 10: Expressions to Create File with Country Name

Click on Flat File Connection Manager and then go to properties and Set Delay Validation=True

Run the SSIS Package, Three files should be created to the path which you have provided in the Expressions. In my case ,Files are created on Desktop as can be seen below
Fig 11: Complete SSIS Package

As we can see three files are created and each file has only data related to that Country. 

Fig 12: SSIS Package Output




SSIS - How To Find The SQL Server Agent Job Names and Steps Where SSIS Package Is Used

The below code can be used to find out all the SQL Server Agent Job Steps which use SSIS Package.

USE [msdb]
GO
SELECT 
   Srv.srvname AS ServerName,
   Job.name AS JobName,
   JStep.step_id,
   JStep.step_name AS StepName,
   JStep.command,
   Job.enabled 
FROM   dbo.sysjobs Job
JOIN   dbo.sysjobsteps JStep
   ON  JStep.job_id = Job.job_id 
JOIN   MASTER.dbo.sysservers Srv
   ON  Srv.srvid = Job.originating_server_id
WHERE  JStep.subsystem='SSIS'
ORDER BY Job.name,step_id

DBA- How To Find Out How Much Percentage Of Database Restoration Is Done?

Sometime we run SQL Server agent jobs to restore databases. As restoration is in progress, the below query can be used to find out how much percentage of restoration is completed.


SELECT command,
       percent_complete,
       start_time,
       Txt.[text] AS Query 
FROM   sys.dm_exec_requests AS DER
       CROSS APPLY sys.Dm_exec_sql_text(DER.[sql_handle]) AS Txt
WHERE  command = 'RESTORE DATABASE'



SSIS- How To Get The Oldest File From Directory In SSIS Package

A short post “How to get the oldest file from directory in SSIS”. As you can see that I have 3 files those were created on different date times.

Fig 1: Source folder with files

We will use script task to get the oldest file name from Source Folder. Let's create two variables as shown below

FolderPath: This variable will contain the Directory path where files exist.
FileName: This variable will be used to hold file name from Script Task so that we can use later in our SSIS Package.

Fig 2: Create variables in SSIS Package

Bring the Script Task to Control Flow Surface and Provide the Variables as shown 
Fig 3: Map variables to Script Task

Use the Below Code in Script Task
/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
namespace ST_d95d08a222ff40f3a02c1dfc7d09a2f4.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

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

        /*
       The execution engine calls this method when the task executes.
       To access the object model, use the Dts property. Connections, variables, events,
       and logging features are available as members of the Dts property as shown in the following examples.

       To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
       To post a log entry, call Dts.Log("This is my log text", 999, null);
       To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

       To use the connections collection use something like the following:
       ConnectionManager cm = Dts.Connections.Add("OLEDB");
       cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

       Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
       
       To open Help, press F1.
   */

        public void Main()
        {
       var directory = new DirectoryInfo(Dts.Variables["User::FolderPath"].Value.ToString());

            FileInfo[] files = directory.GetFiles();
            DateTime lastModified = DateTime.MaxValue;

            foreach (FileInfo file in files)
            {
                if (file.LastWriteTime < lastModified)
                {
                    lastModified = file.LastWriteTime;
                    Dts.Variables["User::FileName"].Value = file.ToString();
                }
            }

            MessageBox.Show(Dts.Variables["User::FileName"].Value.ToString());
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

The highlighted code is what you need to paste in Script Task Editor.

Let's run our SSIS Package and see if it is returning the oldest file name from Directory.
Fig 4: Final output , Package returning the oldest file name

SSIS - How To Create / Use Temp Table In SSIS Package

Scenario:

We have create a SSIS Package for Upsert(Insert/Update). We get csv file with millions of records with (Id,Name,Address columns). If the record come with new Id , we need to insert that record in dbo.Customer table(id, name, address) and for existing IDs we need to update those records.

After doing some analysis, we got to know that the number of records those need to be updated on daily basis are minimum 100,000 per day. To perform above task we can use Lookup Transformation and find out existing and non existing records. Any non-existing IDs can be directly inserted into dbo.Customer table but for update we have to use OLE DB Command transformation. OLE DB Command transformation is slow, it will update one row at a time and for 100,000 records it will take long time.

How about inserting the records into some staging table and write TSQL Statement to Insert/update records? Good idea! It will be fast and easy to do. But my Architect do not want to create a new table :(

Solution:


Ok, How about we create Temp table and then use it in our package to perform the above task and once done, the Temp table will be gone!

Let's start with step by step approach

Step 1:

Prepare Source.csv file on desktop by using below data
Id,Name,Address
1,Aamir,ABC ADDRESS
2,Raza,Test Address
3,July, 123 River Side CA
4,Robert,540 Rio Rancho NM

Step 2:


Create dbo.Customer Table by using below script

USE TestDB
GOCREATE TABLE dbo.Customer
  (
     ID      INT,
     Name    VARCHAR(100),
     Address VARCHAR(100)
  )


Step 3: 

Create SSIS Package to load csv file into dbo.Customer Table.( Insert new records and update existing)
Create OLE DB Connection to the database where your dbo.Customer table exists. Right Click on Connection and then click properties or Click on Connection and press F4 to go to properties. 
Set RetainSameConnection=True. 
Fig 1: Set RetainSameConnection to True for OLE DB Connection


Step 4: 

Create ##Temp table by using Execute SQL Task as shown below by using 
Create Table ##Temp(ID INT, Name VARCHAR(100),ADDRESS VARCHAR(100))
Fig 2: Create ##Temp table by using Execute SQL Task


Step 5: 

Bring Data Flow Task to Control Flow Surface and then connect Execute SQL task to it. Inside Data Flow task bring Flat File Source and make connection to Source.csv file that you have created in Step 1.
Drag Lookup Transformation and configure as shown below. Our goal is to Insert any record which Id does not exist in dbo.Customer table and if ID exists we want to update that records. Instead of using OLE DB Command Transformation, we will insert records which needs to be update in ##Temp table inside Data Flow Task.
Fig 3: Configure Lookup Transformation ( Redirect rows to no match output)

Fig 4: Choose Id from dbo.Customer for lookup

Fig 5: Map the Source Id to dbo.Customer.ID for lookup

Step 6:

Bring OLE DB Destination Transformation from Data Flow Items as shown. Join No Match Output ( new records) of Lookup to OLE DB Destination and choose destination Table (dbo.Customer).
Fig 6: Insert new records by using No Match Output of Lookup Transformation

As we do not want to use OLE DB Command transformation for update inside Data Flow Task. Let's write all records those need to be update into ##Temp table by using OLE DB Destination. We will not be able to see ##Temp table in drop down in OLE DB Destination. Here are two steps we need to take
i) Create a variable with name ##Temp as shown below
Fig 7: TableName variable holding Temp Table Name

ii) Go to SSMS and create ##Temp table ( if you would not create this table, you will not be able to map the columns in OLE DB Destination)
Create Table ##Temp(ID INT, Name VARCHAR(100),ADDRESS VARCHAR(100))

Bring the OLE DB Destination and map to TableName Variable as shown below.
Fig 8: Configure OLE DB Destination to use TableName variable for Destination Table Name.


Fig 9: Map the Source Columns to ##Temp Table Columns

After all the configuration our Data Flow will look like below figure. I renames the transformation to provide better picture about what we are doing in this Data Flow Task.
Fig 10: Data Flow Task with ##Temp Table Destination.

Step 7:

Go to Control Flow Surface and Drag Execute SQL Task to write update statement.

UPDATE DST 
SET DST.Name=SRC.Name
,DST.ADDRESS=SRC.ADDRESS
FROM  dbo.Customer DST
INNER JOIN ##Temp SRC
ON DST.ID=SRC.ID

Fig 11: Execute SQL Task to Update Dbo.Customer from ##Temp 

Our final SSIS Package will look like below
Fig 12: Insert/Update Package by using Temp Table for Updates

If we try to run the SSIS Package, It might complain that ##Temp does not exists. Go to package properties by right clicking in Control Flow Pane and Set DelayValidation=True. By setting DelayValidation we are asking the package not to validate any objects as ##Temp table does not exist at this point and it will be created later in Package. 
Fig 13: Set Delay Validation=True

Run the Package couple of times and check the data in dbo.Customer table. Data should be loaded. Now let's go to Source.csv file and change some values for Name and Address columns and run the package one more time to make sure, Update logic is working fine.

Here is the data after update.
Id,Name,Address
1,Aamir1,Test  ADDRESS
2,Raza1,Test Address
3,July, 123 River Side CA USA
4,Robert,540 Rio Rancho NM

Fig 14: Package Execution After Updating Records in Source.csv file

As we can see that the records are updated, where ever we made changes in Name and Address values.

Fig 16: dbo.Customer data after Upsert