Dynamics 365 Data Migration Using SQL Server SSIS

Prerequisite

  • Install Microsoft Visual Studio (using 2015 in this case).
  • Download and install SSIS Integration Toolkit from Kingsway Soft.

Process

Prepare you source data / structure

Let say we want to export contacts from a SQL database and import that into Dynamics 365. Prepare your source data first. In the following example, we have written a query to export Top 10 Contacts (first name, surname, e-mail, etc.) where the Contact is of Type Person. You can of course, prepare your source data as required.

So, our data is prepared that we would like to fetch from SQL database.

NOTE: In real terms you would be comparing your source data with your destination and creating the query accordingly.

Establish Connectivity

Open up Visual studio, which is the host for SSIS tools area. You don’t necessarily have to have a copy of Visual Studio, you can very well install the SQL Server Data Tools (SSDT) and use that instead.

Create a new Integration Services Project and give it a Name.

One you create the package, it will give all the tools.

Now, we need to establish a connection between the two systems that we are working with (SQL source and dynamics 365 destination). For this we need to create Connection Managers.

Right-click on the Connections Manager area and select New OLE DB Connection.

Click New.

Specify Server Name and Select or Enter a Database Name and click Test Connection.

If connection is successful, click Ok.

So now, we have a source connection and we want to have a target connection. Again right-click on the Connection Managers area and click New Connection.

Select Dynamics CRM (from Kingswaysoft) and click Add.

Configure the CRM Connection Manager by specifying:

  1. Authentication Type – Select “Online Federation (CRM Online – Offfice 365)
  2. CM Discovery Server – Select the option based on your location, for example cm6 is for Australia (Oceania region).
  3. User Name / Password – Provide your Office 365 credentials.
  4. Organization – Select you organization you are subscribed to.

Test Connection and click Ok.

So now we have connections to the SQL database and Dynamics 365.

Map Source to Destination

Insert a Data Flow Task from the SSIS tool box to the Control Flow tab. This means that inside this task we are going to move data from point A to point B.

Now, click Data Flow tab and insert OLE DB Source. You can edit the name of the OLE DB Source and also Edit to configure the settings. For example, in this case we have specified the SQL command which we prepared earlier.

You can also Preview the result of the query. If everything is alright, click Ok.

So, we have a source. Now, we would like to choose a Dynamics CRM Destination object to the Data Flow tab.

Also, drag the blue line from the source and link it to the destination. This means that we are connection the success output from source to the input of the destination.

Now, Edit the Dynamics CRM Destination and configure the CRM Destination Component Editor by specifying:

  1. CRM Connection Manager (select from drop down).
  2. Action (for example, here we want to create new contacts).
  3. Destination Entry (select the CRM table).

To map the fields, click Columns and map the fields from source to destination and click Ok.

Execute

On the Data Flow tab, right-click and click Execute Task.

This will execute the connection and, show you the progress. Once done, the system will also display the number of rows processed.

You can now check the result in Dynamics 365

SSRS Reports for Dynamics 365 Using FetchXML

To be able to create SSRS reports for Dynamics 365 using Visual Studio, you need to perform the following actions:

Pre-Requisites

  • Install Visual Studio 2015. For more information click here.
  • Install Microsoft .NET Framework 3.5 Service Pack 1. Click here to download and install.
  • Microsoft Dynamics 365
    Report Authoring Extension is required to author Fetch-based reports used with Microsoft Dynamics 365 by using SQL Server Data Tools. Click here to download and install.
  • Microsoft SQL Server Reporting Services is required for reporting features in Microsoft Dynamics 365. Click here to download and install.

Once you have completed these steps, you are ready to start creating SSRS reports for Dynamics 365. The following sections explains how you can create SSRS reports using FetchXML from Dynamics 365.

Download Fetch XML from Dynamics 365:

  • In Dynamics 365, click Advance Find.
  • Build a query, for example, to populate all active contact details. You don’t need to save it.
  • Click Download Fetch XML. This file will be used in the report.

  • Open the file in notepad to check the query.

NOTE: We will use this query later in Visual Studio Query Builder.

SSRS Reports Using Fetch XML in Dynamics 365:

  • Open Visual Studio and start a New Project.
  • Click Business Intelligence > Reporting Services > Report Server Project Wizard.
  • Specify project Name and Solution Name.
  • Click Ok.

  • This will open the Reporting Wizard.

  • Click Next to Select the Data Source:
    • Select New Data Source.
    • Specify the Name.
    • Select the Type as Microsoft Dynamics 365 Fetch.
    • Specify the Connection String (this is the URL you use to connect to Dynamics 365)
    • Click Credentials and Use a Specific User Name and Password.
    • Click Ok.

    NOTE: If multiple CRM Organizations are available, there may be dialog box which may ask you to Login or select the right organization. Provide the credentials and Login to relevant organization.

  • Copy and Paste the query from Fetch XML to the Query Builder in Visual Studio and click Run. The system will fetch the result from Dynamics 365.

  • Click OK. The system will display the Query String that you just ran.

  • Click Next. Select the Report Type (Tabular or Matrix).

  • Design the report and click Next.

  • While Completing the Wizard, specify Report Name and select Preview Report and click Finish.

  • The system will show you a preview of how your report will look like in Dynamics 365.

Build the RDL file:

  • In the Solution Explorer, right-click on the Project and click Properties.

  • In the Target Server Version field, select SQL Server 2008 R2, 2012 or 2014. Click Apply. (This is Important to avoid errors!).

  • In the Solution Explorer, right-click on the Project and click Build or Rebuild.

  • In the Solution Explorer, right-click on the Solution and click Open Folder in File Explorer.

  • You will find the RDL file placed in the ..\Client Details Report\bin\Debug folder.

Import the RDL file in Dynamics 365:

  • In Dynamics 365, open the Reports section and click New.

  • In the New Reports form
    • Select Report Type as Existing File
    • Click Choose File to select the RDL file
    • Specify Name
    • Select Display In as Report area

  • Click Save and Close, this will import the report in the Available Report List, from where you can now run the report.

Have Fun!

Connect Power BI Desktop to Dynamics 365

Do you want to connect to hundreds of data sources, simplify data prep, and drive ad hoc analysis, produce beautiful reports, then publish them for your organization to consume on the web and across mobile devices. Well, then you can use the Power BI suite of business analytics tools that deliver insights throughout your organization. You can create personalized dashboards with a unique, 360-degree view of your business, and scale across the enterprise, with governance and security built-in. For more resources on Power BI, click here.

The following article explains how you can connect Power BI Desktop with Dynamics 365.

Copy URL from Dynamics 365

  1. Log in to Dynamics 365.
  2. Click Settings > Customizations > Developer Tools.
  3. Copy the URL under Instance Web API (save it for further use).

Connect Power BI to Dynamics 365

  1. Open PowerBI Desktop and click Get Data > Dynamic 365 (online) and click Connect.
  2. Enter the Web API URL and click OK.
  3. On the OData Feed, click Organizational Account.
  4. Click Sign in to enter your credentials. When you see the message “You are currently sign in.”, you can click Connect.
  5. On the Navigator, select the entities to load into PowerBI and click Load.
  6. The data and entity fields are loaded into PowerBI Desktop. You can now start building reports.

Dynamics 365-Sales Process Overview

Following is a standard process in Dynamics 365. In case your organization follows some additional steps, those can also be incorporated in Dynamics 365 for Sales as well.

  1. The first step in the process is to create a LEAD. A lead can be a potential sale or a potential customer.
  2. Once the lead shows interest in your product or services and asks for more information, it means they are interested. Once they are interested you can qualify that lead and it becomes an OPPORTUNITY. This process is called Lead Qualification. As soon as you do Lead Qualification two things happen:
    1. Whatever information you have captured as an individual in the Lead becomes CONTACT.
    2. Whatever information you have captured as a company in the lead becomes ACCOUNT.

    This is an automatic process and if existing contacts or accounts are available for the lead then the system is going to pick that automatically.

  3. Once you have the opportunity, it means that you have shared the details with the customer, who ask you for more information, so you add products, price, etc. in the Opportunity and created a SALES QUOTE. When you create quotation, you may have to create multiple versions of it because customer might do negotiations with you. All the previous versions are not deleted but saved for history.
  4. Once the customer is interested in product and services and likes the quote, you can then convert the quote to a SALES ORDER. It means it’s a conformation from a customer and the order is created. In the background, the system closes the opportunity with status Won and created an order.
  5. So, once you have received a Purchase order form the customer and you have created the Sales order, you can now go ahead and deliver the product and service you have promised and once you have delivers the products and services, it is time to send them an SALES INVOICE. It means that the work is finished and you are sending an invoice so that the customer can make a payment.

Dynamics 365 – Sales Important Terminology

Want to get started with Dynamics 365 for Sales? Following are basic terminologies that you should be aware of:

Accounts:

  • Accounts is an organization which includes:
    • Customer
    • Vendor
    • Partner
    • Affiliate or Other
  • So, when you are doing business with any company, you capture those company details in the Accounts entity.

NOTE: An Entity is a place where you can store records.

Contacts

  • Contact is an individual.
  • Contact is also your customer, but an individual person.
  • Contact can be associated maximum with one Account, which is called its Primary Contact.
  • For company, you are doing business with, you will need a person whom you are going to interact with. That becomes your primary contact.
  • Therefore, Contacts and Accounts are your customers, where Accounts are organizations and Contacts are individuals.

Leads

  • A Lead is a Prospect and it can be your Potential Customer or Potential Sale. This means that there is a person who has never done a business with you and you have met for the first time and there is a possibility of doing business. On other hand there can be an existing customer who want to work with you on a new Opportunity.
  • Whenever you meet someone, they give you their business card or they share some information about themselves and that becomes your lead.
  • You capture your lead information in Dynamics 365 Sales module.
  • Every sales process starts with a Lead generation or Leads. You interact with them and you start following up with them.

Opportunities

  • Opportunity is a potential sale, which means that the lead is almost ready to buy.
  • You can create an opportunity manually and not necessarily convert a Lead to and Opportunity.
  • Opportunity is attached to an Account or a Contact, because it is essential to know whom are you going to sell. There you identify the customer needs, what are the pain points they have, what exactly is the solution they are looking for and what proposal you have suggested to them.

Quotes

  • When your Lead becomes ready to buy, that becomes an Opportunity and when the prospect is entrusted in your product & Services they may ask you for a Quote.
  • Quote is a document for Prospect & Customer with all the details, such as the product they want to purchase, the quantity that they are looking at, the price you are offering to them, payment terms you have defined and some other important details.
  • Once the quote is ready, you can send it to the prospect or the customer, based on which they can take a decision.

Orders

  • Orders is a confirmation from a customer that they are purchasing your product or services.
  • It can be created from a Quote, which means that you do not have to manually enter it again and all the information from the Quote will be transferred to the Order.
  • Orders can also be created manually, so it is not necessary that you follow the entire process. So in case the customer has already given an order, you can create it directly in Dynamics 365 for Sales.

Invoices

  • It is a document that you use to bill your customer for using your product and services.
  • It is sent to the Customer once you have fulfilled the order. Sending an invoice ensures that the customer is going to pay you for the product and services that they have used.
  • It can also be created manually so that you do not have to follow the entire process, for example in case you have a repeated customer who uses your product and services regularly.

Competitors

  • It is a place where you capture all your competitors
  • When you are bidding for any project and if some other company is also bidding for the same project then you should know what they are good or bad at, what are their strengths and weaknesses, what is their product offering, what kind of quality they offer, etc. So you can record all kind of information about your competitors and keep track of them by attaching it in your sales process.

Sales Literature

  • It is a centralized repository for sales related documents such as Brochures, Product guides, Competitor Information, Pricing & Discounts, etc.

Product Catalogue

  • A product catalogue is a collection of products and their pricing information.
  • When you have product and services, you can created it under Product,
  • You can define a Unit Group, which means how do you sell and purchase a particular product, for example PCs can be sold in numbers and for training you can charge your customers on hourly basis.
  • You can define the Price List based on the Segmentation of your Customers. So you can define multiple price lists.
  • You can define Discount Lists, for example if a customer buys from 0-10, he may be eligible for 1 0 % discount, if he buys from 11-20, he may be eligible for 20 % discount, etc.

Goals

  • Goals are used to keep track of progress on achieving target revenue that you have planned for a particular period (Quarter, Year, etc.)

Goal Metrics

  • Goal metrics explains how the goal number or figure is measured.
  • It is important to define what the goal metrics is, i.e., is it a Count or an Amount. For Sales, it is going to be Amount and say if you want to see how many calls a salesperson has made in a day then that becomes your Count.
  • Actual revenue is calculated through rollup fields, so you can define those rollup fields for calculations for example, whether it is going to be sum of all opportunities that have status 1 or sum of all the Opportunities which are closed.

Now you can dive deeper into the Dynamics 365 Sales process. Cheers!

Dynamics 365 – How to get 30 Day Free Trial?

Just in case you are wondering how to get 30 Day free access to Dynamics 365, then do the following:

  1. Open the URL: https://trials.dynamics.com/Dynamics365/Signup/sales
  2. Select an App (say Sales / Customer Service / etc.).
  3. Specify Work email: <your company e-mail id / office 365 e-mail id>
  4. Specify Phone no: <your phone number>
  5. Click Get Started.

And there you go, you can play around!

Enabling Outlook Add-in for NAV 2017

This article discusses the steps required to Enable Outlook Add-in for Microsoft Dynamics NAV 2017.

Prerequisites

To enabling Outlook Add-in, you must have:

Create User

In this example we are creating a user with SUPER permission and following credentials:

Username: DemoUser

Password: Pass@Word1

For more information, you can refer to the topic How to: Create Microsoft Dynamics NAV Users on MSDN.

Create a New Service Instance

We will create a new service instance for the user:

Server Instance: NAV2017

Ports: 6020,6021,6022 and 6023

For more information, you can refer to the topic How to: Create a Microsoft Dynamics NAV Server Instance on MSDN.

Update Service Tier

Update the Service Tier with the following information:

Credential Type: NavUserPassword

Certificate Thumbprint: <The SSL Certificate Thumbprint>

Update Configuration File for Windows Client

Edit the ClientUserSettings.config file, usually located at the following location:

C:\Users\<user name>\AppData\Roaming\Microsoft\Microsoft Dynamics NAV\100

Update the following parameter:

ClientServicesCredentialType : NavUserPassword

Update Configuration File for Web Client

Edit the web.config file, usually located at the following location:

C:\inetpub\wwwroot\DynamicsNAV100

Update the following parameters:

ServerInstance : NAV2017 (check the settings above)

ClientServicesCredentialType : NavUserPassword

ClientServicesPort : 6021 (check the settings above)

Add Certificate to Web Client

Open the inetmgr command.

This will open the Internet Information Services (IIS) Manager. Do the following:

  • Select Microsoft Dynamics NAV 2017 Web Client.
  • Click Bindings action.
  • On the Site Bindings window click Add.
  • On the Add Site Bindings window, select Type : https and select the SSL Certificate from the drop down menu.
  • Click Ok.

Update Service Tier With URLs

Edit the following parameters in the new Service Tier (NAV2017):

Web Client Base URL : https://<Certificate Name>:443/DynamicsNAV100/WebClient/


SOAP Base URL : https://<Certificate Name>:6022/DynamicsNav/WS/


ODATA Base URL : https://<Certificate Name>:6023/DynamicsNav/OData/

NOTE: Replace the <Certificate Name> with the name of the certificate and also confirm the Port number as per your settings. Enable SSL for SOAP and ODATA. Restart the Service.

Assisted Setup: Set up Outlook for Financials

Open Microsoft Dynamics NAV using the user credentials created earlier.

Open Assisted Setup page (Departments/Administration/Application Setup/General/Assisted Setup) and start Set up Outlook for Financials.

Follow the instructions in the wizard:

The status should change to Completed.

Check your Outlook Client

Open your Outlook client. On the ribbon two new Add-Ins are introduced under Home tab:

You can add Contacts to NAV from Outlook:

You can have Existing NAV contact insight from within Outlook:

You can create new documents from Outlook, such as Sales Quote, Sales Invoice, etc.

Enjoy working with Outlook for Financials 🙂

Test E-Mail Setup for NAV SMTP Mail Setup

 

  1. Open the SMTP Mail Setup window.
  2. Click Apply Office 365 Server Settings (if you have access to Office 365 account). You can ask your administrator for these settings or for testing you can lookup SMTP setting for various email service providers (such Hotmail, etc.).
  3. Choose Authentication as Basic.
    NOTE:When you send email messages multiple times by using an SMTP mail server, you may encounter the following error message: “Unable to write data to the transport connection: An existing connection was forcibly closed by the remote host.”  To resolve this issue, use NTLM or Basic authentication, instead of Anonymous authentication.
  4. Specify your User ID and Password.
  5. Optionally select Secure Connection if your SMTP mail server setup requires a secure connection (such as SSL).
  6. Click Test Email Setup. Select an option in the dialog that pops up and click OK.
    SNAGHTML3a00d93c
  7. You will get a confirmation, if the mail was sent successfully.
    SNAGHTML3a030d72 
  8. Check the result in your mail box.
    image