## 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

## 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

## 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'
,' 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