Is it feasible to use a Script Component as the source for data from multiple Microsoft Dynamics CRM implementations?

1
votes

I have a dynamic list of logins and passwords for Microsoft Dynamics CRM implementations. I am creating an SSIS job that needs to retrieve multiple entities from each of these organizations. Currently, Kingswaysoft has an SSIS integration toolkit for Microsoft Dynamics CRM that I use for retrieving data. Unfortunately, using Kingswaysoft for this would result in at least 60 Dynamics CRM Source components, 120 Sort components, and 60 Merge Joins. Not only would this take forever to create, but it'd also be very difficult to maintain. I would prefer to:

  1. Use a service to query the many Dynamics CRM logins and passwords I need. This has the benefit of working when usernames/passwords change without having to intervene.
  2. Use three Script Components. Each retrieves all the data for one entity type from all organizations.
  3. Use that data to do more things irrelevant to the problem.

Is this a feasible approach? What tools would I need to accomplish this goal? (O365 sdk/restful queries/etc)

If this only works for Dynamics 365 Online, that is ok.

Below is an example of what I need to do for each organizations worth of data.

enter image description here

1
c#restssisdynamics-crmdynamics-crm-online
You could create a master package to retrieve the login data and call the "working package" in a ForEach loop container and parameterize your Dynamics Connection - not sure if this will work for those Sort and Merge tasks you need to do but you can parameterize a lot of stuff that way. - Filburt
Thank you @Filburt, that sounds like a great idea. However, I don't think Kingswaysoft's Source Components support parameterizing logins and passwords. Source: kingswaysoft.com/products/… - Michael Drum
You should be able to parameterize the Crm Connection Manager's ConnectionString property (and as far as I can see UserName and Password as well) - this is what I do when configuring Project Environments for my SSIS Catalog, so I'd expect it to work at package level as well. Try Right-Click your Connection Manager and look for "Parameterize...". - Filburt
This is brilliant and exactly what I was looking for :) - Michael Drum
If you'd like to go for self-answering your post, feel free to copy from my comments and turn them into something useful for others out there. - Filburt

1 Answers

1
votes

To create a (ForEach) Loop Container iterating a set of data you intend to use as parameters add the following items to your "master" package:

  1. Add Package Variable of data type Object to your package - it will contain the Recordset with your parameters.

  2. Add a Data Flow to read your Flat File Data Source
    (would work the same for any data source you can read in your Data Flow)

  3. Add a Recordset Destination to your Data Flow, assign the package variable you created in step 1 to the VariableName Custom Property and select the Input Columns (top checkbox selects all columns)

  4. Switch back to Control Flow and add the Foreach Loop Container

  5. Add as many Package variables as you need to parameterize your worker package connections, tasks, etc. and set their scope to the Foreach Loop Container by selecting them in the Variables list and click the Move Variable icon (box with arrow)

  6. Open the Foreach properties and switch to the Collection tab.
    Change the Enumerator to Foreach ADO Enumerator
    Select your Package variable as the ADO object source variable
    Leave the Enumeration mode with the default Rows in first table

  7. Switch to the Variable Mappings tab and assign the column index of your Recordset columns to the desired target variable you created in step 5.

  8. Add a Execute Package Task to your Foreach Loop Container and connect your worker package on the Package tab.

  9. On the Parameter bindings tab, assign your loop-scoped master package variables from step 5 to the according Child package parameter.