CRM Dynamics Entity data ETL to MSSQL

(0) Share

Report

Posted on by Community Member

Hello there,

I have a task that requires to get the CRM Data Entity data specifically for KnowledgeArticle, Accounts, Contacts, Surveys, and SurveyResponses entities.

Can you please advise if what's the best (Free of charge) way/tool to extract CRM data and load it to MsSQL server? My task is to load the data every 15mins.

So far what I have tried are:

1. DynamicsCRUDOperation - This is the console application that uses your Credentials in "C:\Users\username\AppData\Roaming\CrmServer\Credentials.xml". The issue I encountered here is that my other 3rd party program, VisualCron, runs as SYSTEM. That means the console app won't be able to use my credentials.xml

2. SSIS using KingswaySoft - Was able to create an SSIS package and it's perfectly working. The only issue here is I just found out that it needs a license once you deployed it on my SSIS server.

Thank you so much in advance,

Rome

*This post is locked for comments

All responses (5)

Answers (0)

Community Member on at

Like (0)

Report

RE: CRM Dynamics Entity data ETL to MSSQL

Skyvia can easily ETL all the necessary data from Dynamics to MSSQL on schedule. It has a convenient interface and rather powerful mapping settings, but unfortunately, it is not free for every 15 minutes sync: https://skyvia.com/pricing
Community Member on at

Like (0)

Report

RE: CRM Dynamics Entity data ETL to MSSQL

Nice suggestion, Aric. Will try this one soon.
Suggested answer

Aric Levin - MVP 30,188 Moderator on at

Like (0)

Report

RE: CRM Dynamics Entity data ETL to MSSQL

Why don't you store your credentials in the app.config an encrypt them?

You can run your console application with a parameter /encrypt that will call this logic only once, and then without the parameter will run your process.

You can use the following logic to encrypt it:

// fileName = exeFileName

// sectionName = applicationSettings/AppName.Properties.Settings

Configuration config = ConfigurationManager.OpenExeConfiguration(fileName);

ConfigurationSection section = config.GetSection(sectionName);

ClientSettingsSection clientSection = (ClientSettingsSection)section;

SettingElement password = new SettingElement("Password", SettingsSerializeAs.String);

XmlElement element = new XmlDocument().CreateElement("value");

element.InnerText = passwordValue; // This is the actual password to encrypt

password.Value.ValueXml = element;

clientSection.Settings.Add(password);

section.SectionInformation.ForceSave = true;

config.Save(ConfigurationSaveMode.Modified);

section = config.GetSection(sectionName);

if (!section.SectionInformation.IsProtected)

{

//Protecting the specified section with the specified provider

section.SectionInformation.ProtectSection("DPAPIProtection");

}

section.SectionInformation.ForceSave = true;

config.Save(ConfigurationSaveMode.Modified);

Hope this helps.
Community Member on at

Like (0)

Report

RE: CRM Dynamics Entity data ETL to MSSQL

Thank you, Alex. I tried this one but it didn't work. Also tried to put it in C:\Windows\SysWOW64. What I did it to hard code it for the Credentials.xml and then just used the Windows Scheduler for the scheduling while my account is logged on so that the exe will use my Windows Credential manager. Thanks for the help.
Suggested answer

ashlega 34,477 on at

Like (1)

Report

RE: CRM Dynamics Entity data ETL to MSSQL

Hi,

I don't think you will be able to find a free tool that you can schedule, so, unless you want to write your own console app, maybe try that DynamicsCRUDOperation - just place credentials.xml in this folder:

C:\Windows\System32\config\systemprofile\AppData\Roaming\CrmServer

Might work..