SSIS Talk

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.

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

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:

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.

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.

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