question

bunty avatar image
bunty asked

Split a string in one column to multiple columns using ssis

I have a column in sql server table in which sever name is the data. I need to split the server name in to two columns. Host name goes in to one column and instance name goes in to another column. Any help would be appreciated. Would like to use ssis for this purpose. example :- Input Expected Output column1 Column2 Column3 ServerName Hostname InstanceName wsql1005x\Express wsql1005x Express
ssissplitstring-splitting
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Daniel Ross avatar image
Daniel Ross answered
you can use a Derived Column transform for this, assuming that all the columns to convert contain only one backslash as a separator the first column expression will be LEFT(text,FINDSTRING(text,"\\",1) - 1) and the second column will be RIGHT(text,FINDSTRING(REVERSE(text),"\\",1) - 1) note, you need to replace text with your string to parse, and you need the double backslashes! If you have more than one backslash in the string, I would use a script transform.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.