Whether you are migrating data from legacy to CRM or integrating with other system, you would be required to create staging tables in SQL DB for data transformation and modeling. One way to create is to download metadata using XrmToolbox and manually create SQL script which is lengthy and time-consuming process. In this blog we will explore a quicker way using KingswaySoft.
KingswaySoft is SSIS toolkit, helps you to import and export data from Dynamics 365. You can download it for free for development using free developer license. Refer link for more information.
Once downloaded and installed, you would see KingswaySoft components under SSIS toolbox as shown below:
- Create a new SSIS package and drag Dynamics CRM source component
- Create new Dynamics CRM connection manager using CRM discovery URL as shown below
- In Dynamics CRM source component, use connection created above and select entity for which you need to create SQL table say “account”
- Drag OLE DB Destination component and connect it with Dynamics CRM source component
- Create connection to your SQL database and click New for Table or View
- SSIS will automatically create table script based on entity attributes and their data types from source component