-1

I am currently migrating some data from a holding database to dynamics CRM using SSIS/SSDT with Kingswaysoft plug in example. I currently have a field which stores the value as one line however I require the fields to be split up and migrated to the other three fields as well as leaving the value in the mapped field.

So for example: Storing Database (has data like this)
FIELD A:
ABC-123-321

I require the following once migrated to Dynamics CRM
FIELD A:
abc-123-321
FIELD B
ABC
FIELD C
123
FIELD D
321
So once there is a hyphen It should go the relevant field.

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
KCodeR
  • 121
  • 5
  • 19

2 Answers2

1

You need a derived column transformation before the Kingswaysoft destination, this will allow you to split the column into three before mapping the derived column outputs into the CRM inputs. As long as you specify "add as new column" in the derived column transformation, the original will still be available in the dataflow for mapping.

Gavin Campbell
  • 796
  • 5
  • 19
  • Any idea on how the formula will look like? – KCodeR Oct 25 '16 at 11:26
  • I imagine it will use SUBSTRING. See https://msdn.microsoft.com/en-us/library/ms141069.aspx and https://msdn.microsoft.com/en-us/library/ms137541.aspx for details. – Gavin Campbell Oct 25 '16 at 11:59
  • Been looking at SUB STRING formula, having difficulties in creating the expression to separate each of the dashes as there are 3 dashes. example 123-abc-321 how would u look to – KCodeR Oct 25 '16 at 13:09
  • write a expression for that. – KCodeR Oct 25 '16 at 13:10
  • You need to find the "-" using FindString, then start (or finish) the substrings there as appropriate. There is a similar example here: http://stackoverflow.com/questions/5324590/help-with-substring-in-ssis – Gavin Campbell Oct 25 '16 at 13:41
  • SUBSTRING(FIELDNAME,1,FINDSTRING(FIELDNAME,"-",1) - 1) – KCodeR Oct 25 '16 at 14:12
0

Managed to solve with the following expression.

SUBSTRING(FIELDNAME,1,FINDSTRING(FIELDNAME,"-",1) - 1)
Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
KCodeR
  • 121
  • 5
  • 19