SSIS - Split a file into multiple files based on string pattern

Scenario:

I have a sample file which looks like this 

Test.his

I have a scenario, where in I need to split the above file into multiple files at every occurrence of the word "Group". 

It means, I need to split the above "Test.his" file  into 7 files (Why is it 7 ? Answer: Count the occurrences of the word "Group").

Solution: 

The solution for this gets simpler using an Execute SQL Task , Control Table in the database , For Each Loop Container and Script Task functionality. Lets see how we could achieve this. 

Step 1: 

Lets create a control table in our database. 

CREATE TABLE [dbo].[Test_ControlTable](

[GROUPNAME] [varchar](20) NULL,

[SEARCHINDEX] [varchar](50) NULL

) 

GO

Step 2: 

Execute the below mentioned script to insert rows into the table. 

INSERT INTO [dbo].[Test_ControlTable] VALUES('PATIENT','Group:PATIENT');

GO

INSERT INTO [dbo].[Test_ControlTable] VALUES('APRENURS','Group:APRENURS');

GO

INSERT INTO [dbo].[Test_ControlTable] VALUES('PHYS','Group:PHYS');

GO

INSERT INTO [dbo].[Test_ControlTable] VALUES('PR','Group:PR');

GO

INSERT INTO [dbo].[Test_ControlTable] VALUES('PN','Group:PN');

GO

INSERT INTO [dbo].[Test_ControlTable] VALUES('PROCTIM','Group:PROCTIM');

GO

INSERT INTO [dbo].[Test_ControlTable] VALUES('STUDY','Group:STUDY');

GO

Your SELECT * from [dbo].[Test_ControlTable] gives the below results.

Step 3: 

Create a new project in Visual Studio(BIDS) with Project Name as Test.

Step 4:

Drag and drop Execute SQL Task into work space of the package.

And configure it as below

ResultSet            :  Full Result Set

 SQLStatement  :  SELECT GroupName, SearchIndex FROM [dbo].[Test_ControlTable]

In the Result set tab, 

Click on Add, and give Result name as 0 (Zero) and map it to Object Variable (create one in variables  window as shown in the image )

Step 5:

Drag and drop For Each Loop container and Script Task into the work space as shown below.

This means, the script task runs for every row that is coming from the control table.

Step 6: 

Create two variables in the variables window as shown below. 

                                        

Step 7: 

Configure the For Each Loop container as shown

Step 8: 

Edit the Script Task with the following script:

#region Namespaces
using System;
using System.Text;
using System.Linq;
using System.IO;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Text.RegularExpressions;
#endregion


public void Main()
{
// TODO: Add your code here

            //Declare and Initialize the variables 
            String vFileName = "C:\\Users\\tmhsxk35\\Documents\\Test.his" ;
            String vGroupName = Dts.Variables["User::vGroupName"].Value.ToString();
            String vSearchIndex = Dts.Variables["User::vSearchIndex"].Value.ToString();
            String vSplitFilePath = "C:\\Users\\tmhsxk35\\Documents\\SplitFiles\\";


            // Reading the file 
            String s = File.ReadAllText(vFileName, Encoding.Default);

            //Finding the location of vSearchIndex 
            Match match = Regex.Match(s, @"\b" + Regex.Escape(vSearchIndex) + @"\b",                           RegexOptions.IgnoreCase);

            //If the search is successful, then capture the position of the Index into vPos
            if (match.Success)
            {
                int vPos = match.Index;

                //Assigning the position of next occurence of "Group" to vNextPos
                int vNextPos = s.IndexOf("Group", vPos + 1);

                //Capturing the content between the occurences of "Group" into vContent
                String vContent = s.Substring(vPos, vNextPos - vPos - 2);

                //Writing the content to a file
                StreamWriter sw = File.CreateText(vSplitFilePath + vGroupName + ".csv");
                sw.Write(vContent);
                sw.Flush();
                sw.Close();

                Dts.TaskResult = (int)ScriptResults.Success;
            }  


Dts.TaskResult = (int)ScriptResults.Success;
}

Use the script mentioned above to split the files. 

Save the package. And execute the package to see the file split to 7 files at the path mentioned above. 

Please modify the script as required.

Thanks for taking time to check out the post!

P.S:

 I used For Each Loop Container, Control Table, Execute SQL Task because my searchIndex is a variable. If your searchIndex is constant, you dont have to use them. Just initialize everything in variables inside the script task. 

T-SQL - To convert any format phone number into Standard US format

CREATE FUNCTION [dbo].[fnStandardPhone]
   (
     @PhoneNumber VARCHAR(32)
   )
RETURNS VARCHAR(32)
AS 
   BEGIN

       DECLARE @Phone CHAR(32)

   

       SET @Phone = @PhoneNumber

   

   -- cleanse phone number string

   WHILE PATINDEX('%[^0-9]%', @PhoneNumber) > 0 
           SET @PhoneNumber = REPLACE(@PhoneNumber,
                                      SUBSTRING(@PhoneNumber,
                                                PATINDEX('%[^0-9]%',
                                                         @PhoneNumber), 1),
                                      '')

   

   -- skip foreign phones

       IF ( SUBSTRING(@PhoneNumber, 1, 1) = '1'
            OR SUBSTRING(@PhoneNumber, 1, 1) = '+'
            OR SUBSTRING(@PhoneNumber, 1, 1) = '0'
          )
           AND LEN(@PhoneNumber) > 11 
           RETURN @Phone


     -- build US standard phone number

       SET @Phone = @PhoneNumber

   

       SET @PhoneNumber = SUBSTRING(@PhoneNumber, 1, 3) + '-'
           + +SUBSTRING(@PhoneNumber, 4, 3) + '-' + SUBSTRING(@PhoneNumber, 7,
                                                             4)

   

       IF LEN(@Phone) - 10 > 1 
           SET @PhoneNumber = @PhoneNumber + ' x' + SUBSTRING(@Phone, 11,
                                                             LEN(@Phone) - 10)

   

       RETURN @PhoneNumber

   END


GO

TSQL - To visually identify if there is a Carriage Return / New Line character within the VARCHAR column

Scenario:

We have a scenario, where in we need to know if there is any carriage return('\r') or a new line('\n') character is embedded in a VARCHAR/TEXT column. 

Solution: 

This cannot be simply achieved by using SELECT statement. PRINT function enables us to know the presence of such characters. Let us now see how.

Step 1:

 Let me a create simple table for your easy understanding using the following script. 

CREATE TABLE [dbo].[TestDB_Test1](
[COL1] [int] IDENTITY(1,1) NOT NULL,
[COL2] [varchar](150) NULL
) ON [PRIMARY]

GO

Step 2: 

Lets insert two rows for COL2, one  without carriage return/new line  and the other one with the carriage return/new line. 

-- Sample data without carriage return/ new line character

INSERT INTO [dbo].[TestDB_Test1]

           ([COL2])

     VALUES

           ('I am Line1. I am Line2')

GO

-- Sample data with carriage return/new line character

INSERT INTO [dbo].[TestDB_Test1]

            ([COL2])

VALUES

('I am Line1.' + CHAR(10)+ 'I am Line2')

Step 3: 

Run a SELECT query, to see how the data appears. 

SELECT * from dbo.TESTDB_Test1 

The two rows appears to be same after a SELECT query. 

Step 4: 

Create a variable to hold the value of COL2 using a select query. 

DECLARE @var varchar(150) 

SELECT @var = col2 FROM TestDB_Test1 where col1 = 1

PRINT @var

DECLARE @var varchar(150) 

SELECT @var = col2 FROM TestDB_Test1 where col1 = 2

PRINT @var

This clearly shows that the text in row 2 had a new line character in between.  In order to remove such redundant characters, one could always use REPLACE function. 

SSIS - How to check if a File exists

Scenario:

We have a scenario, in which we have to check if a file exists in a folder. 

If the file exists , delete it and if the file does not exists, create a new one and append some data to it. 

Solution:

We can achieve this, using a simple script in "Script Task" in the Control Flow. 

Lets say, 

File name: Commands.bat 

File Path: I:\Commands.bat

Moving on to how to achieve this.

Step 1: 

Create the following variables at package level.

v_fileName = "Commands.bat" (Give the name of the file that you are checking for)

v_filePath = "I:\" (Give the folder where the file has to be located at)

Step 2: 

Drag and Drop the Script task from the tool box to the work space(control flow) and click on the bubble for ReadOnlyVariables.

Step 3: 

select the input variables for the Script task as shown below. 

Click on OK and then click on Edit Script

Step 4: 

Copy the code which is shown below

 Step 5: 

Execute the task to see the file created. 

Before Executing task

After Executing ScriptTask

SSIS - Executing a set of Command Line Arguments

Scenario: 

We had a requirement, in which a set of command line arguments had to be executed from an executable file (like cmd.exe, curl.exe etc. ) as part of an SSIS package. 

Solution: 

We can achieve this using a batch file and Execute Process Task. Lets see how we could do this in a simple way. 

Step 1: 

Open Business Intelligence Development Studio(BIDS).

Create a sample project by clicking on File - > New - > Project . Select Integration Services Project . Enter a name for the project. 

Create New Project

Step 2: 

Drag and Drop the Execute Process Task from the tool box to the work space. 

Drag and Drop Execute Process Task

Step 3:

Create a Batch file (.bat ) that contains the set of command line arguments that you want to execute. 

Create a new text file , enter all the commands, save the file name as Commands.bat

Set of Arguments

NOTE:
1) The first command has to be the Change the directory to the executable file you are using (Here, curl.exe, in your case it could be cmd.exe or any other executable file)


2) There needs to be line spacing between your arguments. If there is no line spacing, the batch file does not get executed as expected. 

Step 4: 

Configure the Execute Process Task in BIDS. 

1) Under "General" Tab, enter the name

2) Under "Process" Tab, for Executable , browse to the executable file i.e. batch file that has the arguments by clicking on the bubble. 

3) click on OK

Step 5: 

Execute the package to see the arguments in the batch file to get executed . 

Navigate to the curl.exe path to see the sample.csv file created with the data. 

P.S: 

The arguments in line 2 and line 3 are there to import data from a web page and append to sample.csv file.