SSIS – Dynamic substrings based on control table

Last night a user posted to the SSIS Forum a situation where he needed to be able to dynamically substring one field based on the substring rules contained in a table. So I put together a package that does just this. Before we go there though, I just want to mention that there are many ways, progmatically of course, to tackle this problem. The example below strictly follows my interpretations of Bill’s challenge. There is a better way by using the split() function, but never-the-less here’s the example using substring().

First, here’s the setup:

CREATE TABLE [dbo].[segmentControl](
[delim] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Seg1Start] [int] NULL,
[Seg1Len] [int] NULL,
[Seg2Start] [int] NULL,
[Seg2Len] [int] NULL,
[Seg3Start] [int] NULL,
[Seg3Len] [int] NULL,
[Seg4Start] [int] NULL,
[Seg4Len] [int] NULL,
[Seg5Start] [int] NULL,
[Seg5Len] [int] NULL
) ON [PRIMARY]

insert into segmentControl
values (':',1,3,5,3,9,2,12,3,16,3)

CREATE TABLE [dbo].[segmentTest](
[org] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

insert into segmentTest
values ('aaa:aaa:ab:213:azz')
insert into segmentTest
values ('aaa:aab:cc:983:zza')


That sets up our tables. segmentControl contains the rules for the substring function for each segment, up to five. Bill mentioned that five was the most he’d have, so I’ve followed suit. There is a delimiter in that table as well, but it’s not as important as the data we’re working on (segmentTest table, org column) contains the same delimiter. No real reason to use it for the purposes of spliting the field up.Next, we’ll select the delimiter (in this case, there will only ever be one row in the segmentControl table) from the segmentControl table for use later. Create a package scoped variable, delimiterChar, and make it a string type. Next add an Execute SQL task to the control flow. The SQL will be simply, “select delim from segmentControl”. As usual, click on the photos for the larger version.SSIS - Dynamic substring 01

Next, add a data flow. It should look like this:

SSIS - Dynamic substring 02

Inside the data flow, add an OLE DB source and hook it up to segmentTest table. Hook that up to a derived column transformation and add six fields: Segment1, Segment2, …, Segment5, delimiterChar. The setup is below:

SSIS - Dynamic substring 03

From here, go into a lookup component. Join on the delimiter fields, and return all fields from the segmentControl table except for its delim column.

SSIS - Dynamic substring 04

Coming out of the lookup component, go into a script component. Select all fields in the input screen. Edit the script, and use script linked at the bottom of this page. Coming out of the script component you can hook it up to whatever you wish. In this example, I used a Row Count component so that I could simply use a data viewer to see the results.

SSIS - Dynamic substring 05

And that’s about it. The most complex part of this is the script, and credit goes out to the SSIS forum user, jaegd, for posting the script that I used as the foundation for this problem.

Script source: dynamicsubstring.txt

7 thoughts on “SSIS – Dynamic substrings based on control table

  • Faisal

    Awesome Post!!!!

  • JohnAdled

    Excellent post! Really loved it, havent seen an artice this good in a while.

  • Magdalena Sarne

    Discover tart cherry juice concentrate from my nurse for my gout pain. I began getting some a few day back and I feel great. I also got free shipping. Found some excellent books about tart cherry juice

  • Louis Vuitton Outlet Shop

    All of the classy Miu Miu Handbags in this website could become a person’s selection when you imply to buy something amazing. For instance, the cost are generally astonishingly low, that may save much money. For another factor, our prime high quality satisfies this user are considerably. Welcome to choose the inexpensive Miu Miu Handbags.

  • Americanslucky.Altervista.org

    Hello There. I found your blog using msn. This is a
    very well written article. I will

    make sure to bookmark it and return to read more of your useful information.
    Thanks for the

    post. I’ll definitely return.

  • Mariam

    Hi! Do you know if they make any plugins to protect

    against hackers? I’m kinda paranoid about losing
    everything I’ve worked hard on. Any tips?

    Also visit my blog Almoradi Costa Blanca Spain (Mariam)

  • Sunil

    Nice work…

    To get this above results, I have created a script in SQL and may be useful:

    select substring(org, (select Seg1Start from segmentControl), (select Seg1Len from segmentControl)) Segment1,
    substring(org, (select Seg2Start from segmentControl), (select Seg2Len from segmentControl)) Segment2,
    substring(org, (select Seg3Start from segmentControl), (select Seg3Len from segmentControl)) Segment3,
    substring(org, (select Seg4Start from segmentControl), (select Seg4Len from segmentControl)) Segment4,
    substring(org, (select Seg5Start from segmentControl), (select Seg5Len from segmentControl)) Segment5 from segmentTest

Comments are closed.