ssis- split a single string into multiple columns

Question

I have a string which looks like

"v" : {"cns":"AQRV3Z","elapsed-ms":"1","key-hash":"377515780","key-size-b":"116","load-time-ms":"250","lower-bound-ms":"20","outcome":"done","value-size-b":"3124"}.

I want to separate the "load-time-ms" and "outcome" to be in two new columns like col1-load time and col2- outcome which will be populated with the corresponding values.

I am kind of stuck with the substring fucntion. Any help would be greatly appreciated.

Thanks !

are you wanting to do this in ssis derived column or in tsql in the source definition? The answer is very different syntax on how to do it — Matt
– Matt, Commented Aug 16, 2016 at 17:42
What do you mean you are "stuck with the substring function"? — Tab Alleman
– Tab Alleman, Commented Aug 16, 2016 at 18:21

Dance-Henry · Accepted Answer · 2016-08-16 19:03:59Z

3

First Solution: TSQL in the SOURCE sql command. I use the temp table, change the table name accordingly please. Tested, works perfect in SSIS. :)

--create table structure
create table #test (strg varchar(1000))
go


--insert into sample data
insert #test values ('"v" : {"cns":"AQRV3Z","elapsed-ms":"1","key-hash":"377515780","key-size-b":"116","load-time-ms":"250","lower-bound-ms":"20","outcome":"done","value-size-b":"3124"}')


--below is solution for the data source
select substring(   strg,
                    charindex('"load-time-ms"',strg)+len('"load-time-ms"')+2,
                    charindex(  '"',
                                substring(  strg, 
                                            charindex('"load-time-ms"',strg)+len('"load-time-ms"')+2,
                                            len(strg)))-1) as [col1-load time],
       substring(   strg,
                    charindex('"outcome"',strg)+len('"outcome"')+2,
                    charindex(  '"',
                                substring(  strg, 
                                            charindex('"outcome"',strg)+len('"outcome"')+2,
                                            len(strg)))-1) as [col2-outcome]
from #test

RESULT:

In SSMS:

In SSIS:

Second Solution: Derived column in SSIS, tested, works perfect. :)

col1-load-time

SUBSTRING(strg,FINDSTRING(strg,"\"load-time-ms\"",1) + LEN("\"load-time-ms\"") + 2,FINDSTRING(SUBSTRING(strg,FINDSTRING(strg,"\"load-time-ms\"",1) + LEN("\"load-time-ms\"") + 2,LEN(strg)),"\"",1) - 1)

col2-outcome

SUBSTRING(strg,FINDSTRING(strg,"\"outcome\"",1) + LEN("\"outcome\"") + 2,FINDSTRING(SUBSTRING(strg,FINDSTRING(strg,"\"outcome\"",1) + LEN("\"outcome\"") + 2,LEN(strg)),"\"",1) - 1)

edited Aug 16, 2016 at 19:03

answered Aug 16, 2016 at 17:56

Dance-Henry

9531 gold badge8 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

guest678 Over a year ago

Hi Henry,Thanks for the code in T-sql. I am looking to do it in ssis derived column as my destination will be a flat file and not an oledb destination. But Thanks a lot !

Dance-Henry Over a year ago

It works fine for FF destination too. I updated the answer, check it out. :)

Matt Over a year ago

It is more a function of your source that your destination. If you are using a sql-server source @Dance-Henry solution works great because you just add the dynamic columns to your sql select statement. then there is no need for a derived column. But if your source is not sql then yes you have to use a derived column. so it is kind of preference based if source allows for it.

Dance-Henry Over a year ago

@guest678 I updated the answer and include the Derived Column code. Enjoy. :)

Collectives™ on Stack Overflow

ssis- split a single string into multiple columns

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related