1

I am new to SSIS. I want to retrieve newly inserted value from destination CRM that is Identity column

My source is SQL server and destination is CRM database

jasonscript
  • 6,039
  • 3
  • 28
  • 43
Ragunath Kalimuthu
  • 69
  • 1
  • 4
  • My source is SQL server and destination is CRM database – Ragunath Kalimuthu Jul 18 '18 at 05:06
  • How exactly are you inserting it? Through an ADO.Net connection? Through a SQL task? Are you inserting a batch of records or only one? Do you _have_ to use Kingswaysoft to find it? – Nick.Mc Jul 18 '18 at 05:16
  • Right now I am using kingswaysoft for inserting in to destination CRM it creates guid primary key . I want that key for reference to insert in to child tables – Ragunath Kalimuthu Jul 18 '18 at 05:20
  • So I guess you have a dataflow feeding into that, which means you have a bunch of records going in. Kingswaysoft would have to have an output for this. If it doesn't (which is likely) then you need to use other methods such as staging tables or script to do this. Even this is a common requirement, I've never found a simple way to do this. – Nick.Mc Jul 18 '18 at 05:32

2 Answers2

2

There is a CrmRecordId column in the output of the destination component. This means you can't see it in the Insert component, but you can access it in child steps.

There is also an IsNew column that may be of use (e.g. if you're doing an Upsert)

enter image description here

  1. Shows the Insert / Upsert Kingswaysoft Step
  2. Shows the destination component
  3. shows the available columns including CrmRecordId and IsNew
jasonscript
  • 6,039
  • 3
  • 28
  • 43
1

In your destination step, you will find a checkbox for CrmRecordId. It will be check by default.

enter image description here

Then, you can use the id from the destination node. In my case, I count the number I created and log the guids.

enter image description here

Nick
  • 735
  • 5
  • 14
  • I've never used Kingswaysoft before - that _actually_ looks pretty useful. – Nick.Mc Jul 18 '18 at 23:04
  • @Nick.McDermaid it is also possible to store source primarykey id and destination keyid after record moved in to destination? – Ragunath Kalimuthu Jul 19 '18 at 02:21
  • Yes it is and that is how I do it with staging tables. However the source key needs to be stored in a column in the target table - that's the only way you can know which row got which ID. I doubt you can add columns to Dynamics tables – Nick.Mc Jul 19 '18 at 23:07