Remove Special Characters from String and Convert to Multiple Columns by using ParseName Function - SQL Server / TSQL

Scenario:


Sometime we get the data in one column with some kind of delimiter (such as comma, pipe, space etc.) and also this string contains some special characters such as (.,./,$,#,@ etc.).
The current problem is related to the string that has space separator and some special characters. Our goal is to split this string into First Name, Middle Name and Last Name by using space as separator.

Solution:


Step 1: 
Let's create test table with some sample data

CREATE TABLE dbo.colsplit 
  ( 
     fullname VARCHAR(300) 
  ) 

go 

INSERT INTO dbo.colsplit 
VALUES      ( 'CHARLES. ROSAMILIA.') 

go 

INSERT INTO dbo.colsplit 
VALUES      ( 'CHARLES. .cc ROSAMILIA. @') 

go 

INSERT INTO dbo.colsplit 
VALUES      ( 'CHARLES. HANS. ROSAMILIA /') 

go 

INSERT INTO dbo.colsplit 
VALUES      ( 'CHARLES ROSAMILIA') 

go 

SELECT * 
FROM   dbo.colsplit 

How to Remove Special Characters from string and convert single value to multiple values in TSQL


Step 2:
Let's create the function that we can use to remove special characters from the string in SQL Server.

CREATE FUNCTION [dbo].[RemoveSpecialCharacters] ( @InputString NVARCHAR(max) ) 
returns VARCHAR(max) 
BEGIN 
  DECLARE @OutputString NVARCHAR(max ) 
  IF (Isnull(@InputString,'')='') 
  SET @OutputString = @InputString 
  ELSE 
  BEGIN 
    DECLARE @Length      INT 
    DECLARE @Counter  INT 
    DECLARE @CharCode INT 
    SET @OutputString = '' 
    SET @Length = Len(@InputString) 
    SET @Counter = 1 
    WHILE @Counter <= @Length
    BEGIN 
      SET @CharCode = Ascii(Substring(@InputString, @Counter, 1)) 
      IF @CharCode=32 
      OR 
      @CharCode BETWEEN 48 
      AND 
      57 
      OR 
      @CharCode BETWEEN 65 
      AND 
      90 
      OR 
      @CharCode BETWEEN 97 
      AND 
      122 
      SET @OutputString = @OutputString   + Char(@CharCode) 
      SET @Counter = @Counter + 1 
    END 
  END 
  RETURN @OutputString  
END



Step 3: 
Let's write out final query, we will use
RemoveSpecialCharacters function to remove special characters.
ParseName builtin function to split the Full Name depending upon ''( space).
Wrote Case Statement to adjust middle name to last name where there is no last name.

;WITH cte 
     AS (SELECT Reverse(Parsename(Replace(Reverse( 
                                  dbo.[Removespecialcharacters](fullname)), ' ', 
                                             '.'), 1 
                        )) AS [FirstName], 
                Reverse(Parsename(Replace(Reverse( 
                                  dbo.[Removespecialcharacters](fullname)), ' ', 
                                  '.'), 2 
                        )) AS [MiddleName], 
                Reverse(Parsename(Replace(Reverse( 
                                  dbo.[Removespecialcharacters](fullname)), ' ', 
                                  '.'), 3 
                        )) AS [LastName] 
         FROM   dbo.colsplit) 
SELECT firstname, 
       CASE 
         WHEN lastname IS NULL THEN NULL 
         ELSE middlename 
       END AS MiddleName, 
       CASE 
         WHEN lastname IS NULL 
              AND middlename IS NOT NULL THEN middlename 
         ELSE lastname 
       END AS LastName 
FROM   cte 


The above query can be changed according to the requirement. We can also write a user function to split string into multiple columns. Here we were required to split First Name,Middle Name and Last Name and that can be handled with built in function (ParseName).
How to Split single Column into Multiple Columns in TSQL - SQL Server Tutorial

How to Execute .SQL files by using SSIS Package - SSIS Tutorial

Scenario:

Sometime we have requirement to execute .sql files in SSIS. Let's assume that we have some .sql files in one of the folder those contain different DDL Statements, DML ( update, delete etc.) statements.We need to loop through all .sql files and run from the folder.

Solution:


Step 1:
First of all we have to use For-each loop so we can loop through these files. Configure you For-each loop as show in figure below.
How to execute dot SQL files from a folder in SSIS Package - SSIS Tutorial

Then click on Variable Mapping and create a new variable to save the file name.
Add variable in For-Each Loop container to save .sql file name - SSIS Tutorial

Step 2:
Drag Execute SQL Task to Control Flow Pane and then configure as shown in Figure below. As we are using the .sql file to run , so instead of direct input we used file connection and then made connection to one of the .sql file.
Create connection to one of the dot SQL file and choose Connection Manager on which you would like to run .sql files

Step 3: 
As we need to loop through the files in folder and run each of the .sql file.We will be using expressions on connection manager for file. Each time the For-each Loop will pick one file and then connection will be updated and .sql will be executed.

Write expression to update connection manager path for new file on each iteration of For-each Loop Container


Step 4:
Your SSIS Package is ready and it should be able to execute any number of Dot SQL files from a folder for you. Go ahead and execute and see if all works as expected.



Video Demo : How to execute dot(.) sql files from a folder in SSIS Package
Execute multiple .SQL files from a folder in SSIS Package

SSIS - Add Update Columns dynamically [Execute SQL Task in SSIS]

Scenario :

Sometime we write queries in our Execute SQL to update some column values. Let's say we start with updating one or two columns but later we needed to add third column in Update query. To handle this situation we have to open our SSIS Package every time and update update query. We want to achieve this without opening our SSIS Package.

Solution:


Step 1: 
Create definition table for your Update Queries, this table will have source table name and column names those needs to be updated.
Have  SourceTableName, DestnationTable name,Column Names and Active(Y,N) in our definition table so we can write our dynamic query by using these column values.

CREATE TABLE dbo.UpdateDef (
    SourceTableName VARCHAR(100)
    ,DestinationTableName VARCHAR(100)
    ,ColumnName VARCHAR(100)
    ,JoiningKey VARCHAR(100)
    ,Active CHAR(1)
    )
GO

INSERT INTO dbo.UpdateDef
VALUES (
    'dbo.Src'
    ,'dbo.Dest'
    ,'NAME'
    ,' dbo.Dest INNER JOIN dbo.Src ON D.ID=S.ID AND D.NAME=S.NAME'
    ,'Y'
    )
GO

INSERT INTO dbo.UpdateDef
VALUES (
    'dbo.Src'
    ,'dbo.Dest'
    ,'ADDRESS'
    ,' dbo.Dest INNER JOIN dbo.Src ON D.ID=S.ID AND D.NAME=S.NAME'
    ,'N'
    )
GO

INSERT INTO dbo.UpdateDef
VALUES (
    'dbo.Src'
    ,'dbo.Dest'
    ,'AGE'
    ,' dbo.Dest INNER JOIN dbo.Src ON D.ID=S.ID AND D.NAME=S.NAME'
    ,'N'
    )
GO

Step 2: 
Run this query in Execute SQL Task as shown below

DECLARE @VARSQL varchar(max)
SET @VARSQL = (SELECT DISTINCT
  REPLACE(COL, '=SET', ',SET') AS QUERY
FROM (SELECT
  'Update  D ' + (SELECT
    STUFF((SELECT
      N'=SET D. ' + ColumnName + N'=S. ' + ColumnName
    FROM dbo.UpdateDef I
    WHERE I.Active = 'Y'
    FOR xml PATH (''), TYPE)
    .value('text()[1]', 'nvarchar(max)'), 1, 1, N''))
  + ' FROM ' + JoiningKey
  AS COL
FROM dbo.UpdateDef O
WHERE DestinationTableName = 'Dbo.Dest'
AND Active = 'Y') D)

PRINT @VARSQL
EXEC (@VARSQL)


How to add Update Column Dynamically in SQL Server in SSIS Package without Opening SSIS Package