Change Tracking – incremental data loading into DWH

Task: to create a SSIS package to incremental data loading by means of Change Tracking

The process of creating a SSIS solution consists of the following steps:
1. creating of a SSDT (SQL Server Data Tools) SSIS project
2. enabling of the ‘CHANGE TRACKING’ feature on a database and table level
3. creating of the ‘MySettings’ table and inserting a default value into the ‘LastVersion’ column
4. creating of the ‘Version_Get’ stored procedure
5. getting and setting of the ‘@LastVersion’ and ‘@CurrentVersion’ variables
6. creating of the FAKE DWH ‘BusinessEntity_DWH’ table
7. FAKE inserting into the ‘BusinessEntity’ table
8. incremental data loading into the fake DWH ‘BusinessEntity_DWH’ table (the core of ‘CHANGE TRACKING’)
9. saving of the ‘@CurrentVersion’ variable into the ‘LastVersion’ column
10. disabling of the ‘CHANGE TRACKING’ feature on a table and database level; deleting of created objects

Figure 1: Change Tracking – Control Flow

Figure 2: Change Tracking – Data Flow

Source code: SSIS Package (SQL Server 2012 – SSDT)
Note: do not forget to customize the ConnectionString in the Project.params setting

Additional references:

• Change data capture
  by Wikipedia
• Change Data Capture
  by MSDN
• Change Tracking
  by MSDN
• Configuring Change Tracking
  by MCTS EXAM 70-433
• Features Supported by the Editions of SQL Server 2012
  by MSDN
• Incremental Data Loading Using CDC
  by Mark Murphy
• Introduction to Change Data Capture (CDC) in SQL Server 2008
  by Pinal Dave
• Tuning the Performance of Change Data Capture in SQL Server 2008
  by TechNet

Data cleaning – selection of data from two data sources that contain duplicate data

Task: to create a SSIS package to delete duplicates by means of Fuzzy logic

The process of creating a SSIS solution consists of the following steps:
1. creating of a SSDT (SQL Server Data Tools) SSIS project
2. defining of connection to data sources
3. defining of connection to saving duplicate records
4. defining of connection to saving error messages
5. defining of connection to a database
6. creating of a table in a target database
7. creating of a Data Flow diagram
8. removing of exact duplicates
9. removing of “similar” duplicates by means of Fuzzy logic
10. saving of retrieved duplicates

Figure 1: Deleting duplicates by means of Fuzzy logic

Source code: SSIS Package (SQL Server 2012 – SSDT)
Note: do not forget to customize the Connection Managers in solution setting

Additional references:

• Features Supported by the Editions of SQL Server 2012
  by MSDN
• SQL SERVER – Why Do We Need Data Quality Services – Importance and Significance of Data Quality Services (DQS)
  by Pinal Dave

SSIS basics – import CSV to a SQL Server table

Task: to give a basic look at the import of a CSV file to a SQL Server table by means of SSDT by Microsoft Visual Studio

Sales per region,Africa,Asia,Europe
January,34,67,56
February,36,87,78
March,31,56,88
April,29,67,92
May,54,71,68
June,68,71,54
July,54,71,68
August,68,71,54
September,92,67,29
October,88,56,31
November,78,87,36
December,56,67,34

Source code 1: CSV data

USE [Test]
GO

CREATE TABLE [dbo].[SSIS_CSV_Import]
(
    [Month]      [varchar](9)    NOT NULL,
    [SaleAfrica] [numeric](2, 0) NOT NULL,
    [SaleAsia]   [numeric](2, 0) NOT NULL,
    [SaleEurope] [numeric](2, 0) NOT NULL
)
GO

Source code 2: Table structure

Figure 1: Flat File Connection Manager Editor – General

Figure 2: Flat File Connection Manager Editor – Columns

Figure 3: Flat File Connection Manager Editor – Advanced

Figure 4: SQL Destination Editor – Connection Manager

Figure 5: SQL Destination Editor – Mappings

Figure 6: SSIS Package - Data Flow

SELECT [Month], [SaleAfrica], [SaleAsia], [SaleEurope]
FROM   [dbo].[SSIS_CSV_Import]
GO
/* Result:
   Month       | Africa | Asia | Europe
   January       34       67	 56
   February      36       87     78
   March         31       56     88
   April         29       67     92
   May	         54       71     68
   June	         68       71     54
   July	         54       71     68
   August        68       71     54
   September     92       67     29
   October       88       56     31
   November      78       87     36
   December      56       67     34
*/

Source code 3: SQL query for result verification

Additional references:

• Comma-separated values
  by Wikipedia
• SQL SERVER – Import CSV into Database – Transferring File Content into a Database Table using CSVexpress
  by Pinal Dave
• SQL SERVER – Importing CSV File Into Database – SQL in Sixty Seconds #018 – Video
  by Pinal Dave
• SQL SERVER – Powershell – Importing CSV File Into Database – Video
  by Laerte Junior
• SQL SERVER – Running SSIS Package in Scheduled Job
  by Pinal Dave

CSV – row values concatenation (FOR XML PATH) and exporting column values to a MS Excel file (Microsoft.Jet.OLEDB)

Task: to create row values concatenation, i.e. CSV (Comma Separated Values), by means of the FOR XML PATH construction, and to export database table column values to the MS Excel file using the Microsoft.Jet.OLEDB ad hoc method of connecting and accessing remote data by using OLE DB
Solution: see Source code 1 – 4

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'ad hoc distributed queries', 1;
GO
RECONFIGURE;
GO

Source code 1: system configuration

USE [AdventureWorks]
GO

DECLARE @InputData        AS TABLE
(
        [ManagerCounter]  INT,
        [ManagerLoginID]  NVARCHAR(256),
        [EmployeeLoginID] NVARCHAR(256)
)
;WITH [Employees]
(
      [ManagerCounter],
      [ManagerID],
      [LoginID]
)
AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY [ManagerID] ORDER BY [ManagerID]),
           [ManagerID],
           [LoginID]
    FROM   [HumanResources].[Employee]
)
INSERT INTO @InputData
(
            [ManagerCounter],
            [ManagerLoginID],
            [EmployeeLoginID]
)
SELECT   [ManagerCounter],
         [Managers].[LoginID],
         [Employees].[LoginID]
FROM     [HumanResources].[Employee] AS [Managers] INNER JOIN [Employees] ON
         [Managers].[EmployeeID] = [Employees].[ManagerID]
ORDER BY [Managers].[LoginID]

/* temporary test */
SELECT    [ManagerCounter]  AS 'ManagersCounter',
          [ManagerLoginID]  AS 'Managers',
          [EmployeeLoginID] AS 'Employees'
FROM      @InputData
ORDER BY  [ManagerLoginID]

Source code 2: input data preparation (ManagersCounter – number of employees per manager)

Figure 1: MS SSMS result of input data preparation (ManagersCounter - number of employees per manager)

SELECT       [Managers].[ManagerLoginID] AS 'Manager',
(
    SELECT
        CASE
	     WHEN [Employees].[ManagerCounter] = 1
                  THEN [Employees].[EmployeeLoginID]
	     ELSE ',' + [Employees].[EmployeeLoginID]
	END
    FROM     @InputData AS [Employees]
    WHERE    [Employees].[ManagerLoginID] = [Managers].[ManagerLoginID]
    ORDER BY [Employees].[ManagerLoginID]
    FOR XML  PATH('')
)            AS 'Employees'
FROM         @InputData AS [Managers]
GROUP BY     [Managers].[ManagerLoginID]

Source code 3: row values concatenation (converting to the CSV format) by means of FOR XML PATH

Figure 2: MS SSMS result of row values concatenation (converting to the CSV format) by means of FOR XML PATH

INSERT INTO OPENROWSET
(
            'Microsoft.Jet.OLEDB.4.0',
            'Excel 8.0;Database=C:\Managers.xls;',
                -- NOTE: the MS Excel file must already exist on the disk 
                --       together with the specified path and name!
            'SELECT [Managers] FROM [Managers$]'
                -- NOTE: the above-mentioned MS Excel file must have one 
                --       sheet named Managers, and the value in cell [0, 0], 
                --       i.e. the first row and column, must be 
                --       set to Managers (see Figure 5)!
)
SELECT	    [Managers].[ManagerLoginID]
FROM	    @InputData AS [Managers]
GROUP BY    [Managers].[ManagerLoginID]

GO

Source code 4: exporting data (the column Managers) to the MS Excel file on the disk

Figure 3: MS SSMS result of exported data (the column Managers) to the MS Excel file on the disk - error

Figure 4: MS SSMS result of exported data (the column Managers) to the MS Excel file on the disk - solution for the above error

Figure 5: MS SSMS result of exported data (the column Managers) to the MS Excel file on the disk

Additional references:

• Concatenating Row Values in Transact-SQL
  by Anith Sen
• Data truncated to 255 characters with Excel ODBC driver
  by Microsoft
• Execute SQLTask Error String or binary data would be truncated
  by MSDN
• Export to Excel
  by SQL Team
• OPENROWSET (Transact-SQL)
  by MSDN
• SQL Memory and External Data
  by Nathon Dalton
• SQL SERVER – 2005 – Export Data From SQL Server 2005 to Microsoft Excel Datasheet
  by Pinal Dave
• SQL SERVER – 2005 – Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE
  by Pinal Dave
• SQL SERVER – Comma Separated Values (CSV) from Table Column
  by Pinal Dave
• SQL SERVER – Create a Comma Delimited List Using SELECT Clause From Table Column
  by Pinal Dave