Javed Iqbal

Tibco Scribe vs Kingswaysoft and where lies PowerAutomate aka MS Flow

/i , /af

3rd party Integration Tools for Dynamics 365

After spending several years of building integration between Dynamics 365(CRM) and other systems, we thought of comparing these two widely used 3rd party integration tools.

(Beware of the publication dates because of the innovative nature of the IT industry)

Before comparing these 2 integration tools it is worth noting Microsoft’s power automate called MS Flow before rebranding.

Power Automate

Microsoft built this cloud tool to automate the process/data flow within a system or between systems, which are either connected through CDS (common data service) or provided connectors.
For integration, this tool can be considered for the simple flow of data between systems without complex if-else scenarios

Here are some of the reasons why it is not considered an integration tool:

  • No Upsert (UpdateInsert) functionality
  • No lookup/seek functionality (one must list records and handle them)
  • No parent-child relation (difficult to process transactions)
  • The trigger-based event cannot filter on fields (update trigger cannot be restricted to certain fields)
  • Data manipulation is difficult which requires complex expressions

Beside all these limitations PowerAutomate is still a handy cloud-based tool for automatic processing of functions and simple data flow.

Enough of PowerAutomate, let’s get to our main topic of comparing Tibco Scribe and Kingswaysoft with their respective pros/cons

Tibco Scribe vs Kingswaysoft

 

SSIS_Kingswaysoft

Tibco scribe (insight/online)
Requires SQL Server integration services No SQL or SSIS required works independently
Existing SQL server with SSIS can host kingswaysoft toolkit Requires separate server for scribe insight though not for online
No queue-based integration Requires windows MSMQ (message queuing) for queue-based integration
Requires development environment with Visual Studio, SSIS and kingawaysoft_toolkit Scribe online requires no installation or dev environment setup, though Scribe insight requires full server installation
No trigger-based integration
Though toolkit has a complex EntityChanges mechanism to achieve trigger-based integration which is based on Input/output token variables and DB tables		 Trigger-based integration is very easy through the provided adapter using a publisher for scribe insight (on-premise)
For Scribe Online we must generate endpoint for event-based integration and then extend the source system to send data to the endpoint to achieve trigger-based integration.
Requires experience of SSIS for complex integration scenarios No SSIS or other such experience required
No standard/persistent mechanism for error logging Very persistent and intuitive mechanism for error logging including transactional errors
Free developer edition Not free, only one month’s trial
Much Better performance especially for large datasets because of SSIS based processing Not so efficient in performance when compared to SSIS based solutions
Can be very difficult to build DTS for complex integrations which require SSIS scripting, especially if one dosn’t have SSIS experience No coding or scripting required, though sometimes one must build strong logics using DTS steps and flow operations
A small set of source/target-system dependent functions and that’s why SSIS scripting comes into play to achieve certain requirements which can be cumbersome A large set of source/target-system dependent functions at our leverage
No integration centric database Has its own integration centric database (scribe insight)
Once the development environment is set with SSIS and kingswayssoft toolkit and Visual Studio. Development of integration maps is a seamless experience Scribe online sometime is very slow and irritating with long response time especially while building integration maps and debugging, but that is not the case for Scribe insight luckily
Very difficult error tracing Very easy error tracing due to the extensive error logging built-in mechanism
Much cheaper than Scribe Costly. At least 4 times more expensive than kingswaysoft

 

Conclusion:

Tibco Scribe (insight) is great for complex integration where error tracing is very important, having no developer resources.

Kingswaysoft is great for companies with low budget for integration where error tracing is not so important and have some developer resources, though it will require more time to build integration maps.

We hope it will help someone in making the right choice and apologies if we missed something 🙂

https://flow.microsoft.com/

https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365

https://www.tibco.com/solutions/microsoft-dynamics-365-integration

Javed Iqbal

Dynamics 365 (CRM) on-premise cannot login using Plugin Registration Tool or Custom App using Tooling connector.

/i , /af

If all of a sudden you are unable to login via PluginRegistrationTool or other apps, reason can be the recent windows security update on CRM Server.

2020-01 Cumulative Update for Windows Server 2016 for x64-based Systems (KB4534271)

Removing this update restored our connection 🙂

Javed Iqbal

Dynamics 365 CRM update/upgrade error: The UPDATE statement conflicted with the FOREIGN KEY constraint “solution_base_dependencynode”

/i , , /af

Recently while upgrading (import db) CRM deployment from 2016 to v9 we encountered this strange error. After trying everything including mentioned her and running short of time, we opened MS ticket and got a script to run on organisation’s DB, which actually resolved this issue.
Script actually drops foreign key constraints so should be used carefully and at your own risk.

IF OBJECT_ID(‘tempdb..#DropForeignKeysStatement’) IS NOT NULL DROP TABLE #DropForeignKeysStatement

SELECT DropStatement = ‘ALTER TABLE [dbo].[‘ + st.name + ‘] DROP CONSTRAINT [‘ + sfk.name + ‘]’
INTO #DropForeignKeysStatement
FROM sys.foreign_keys sfk
INNER JOIN sys.tables st ON sfk.parent_object_id = st.object_id
AND sfk.schema_id = st.schema_id
WHERE sfk.schema_id = schema_id(‘dbo’)

SELECT DropStatement FROM #DropForeignKeysStatement

DECLARE dropStatementCursor CURSOR FOR
(SELECT DropStatement FROM #DropForeignKeysStatement);

OPEN dropStatementCursor;

DECLARE @currentStatement NVARCHAR(max);

FETCH NEXT FROM dropStatementCursor INTO @currentStatement;

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @currentStatement;

FETCH NEXT FROM dropStatementCursor INTO @currentStatement;
END

CLOSE dropStatementCursor;
DEALLOCATE dropStatementCursor;

IF OBJECT_ID(‘tempdb..#DropForeignKeysStatement’) IS NOT NULL DROP TABLE #DropForeignKeysStatement

Javed Iqbal

Dynamics 365(CRM) Organization Update Import Failed in perform Action Install of Solution=CustomControlsCore …

/i , /af

We encountered the following error while updating one of our client’s organization from 8.0 to 8.2x. Other organizations got updated without any issue but this one just won’t.

Error| Failed in perform Action Install of Solution=CustomControlsCore, FileName=CustomControls.zip, Version=8.2.9.19, Action=Install for OrgId=xxx

Since other organizations are getting updated without a problem we can imagine the issue is with the organization’s database.
After a rigorous googling and getting through a lot of org. import/update issues we fixed the problem as follows:
Problem: Our CRM base language is danish but the SQL user performing org update has default language English…
Solution: change the user’s default language to danish as is of CRM.
To find user’s default language simple open SQL_Management_Studio and under Security select properties of the user and the default language will be shown in the bottom and simply change it to one which is of CRM, or run SQL:
SELECT default_language_name
FROM sys.server_principals
WHERE name = '<user>'

To find available language name in SQL server:
SELECT name,alias FROM sys.syslanguages WHERE name = '<user>'

To set the default language for the user:
Exec sp_defaultlanguage ‘<user>’,'<languageName>’

Try updating organization from Deployment Manager and it would go smooth 🙂

Javed Iqbal

Dynamics 365(CRM) on-premise huge Attachments size SQL fix (Update/Delete)

/i , /af

While upgrading one of our client we found huge size of database where almost 75% of the DB size was taken by the Attachments table.
Her is the sql script which is actually just updating the body field to release size occupied by large attachments. Be careful while using it as it might take several hours depending on the number of records. Batch size is set to 1000 which can be modified according to the situation. Script processes emails with no activities for more than 2 years which can be adjusted as required.

DECLARE @Count INT=1, @Total INT=0
WHILE @Count>0
BEGIN
BEGIN TRANSACTION;
UPDATE dbo.Attachment
SET
Body = ‘QXR0YWNobWVudCB3YXMgYXJjaGl2ZWQgLSA5LzIwMTku’, — BASE64 for “Attachment was archived – 9/2019.”
FileSize = 34,
MimeType = ‘text/plain’,
FileName = FileName + ‘.txt’
WHERE AttachmentId in (
select TOP 1000 ATT.AttachmentId — Batchsize=1000
FROM dbo.Attachment ATT
INNER JOIN ActivityMimeAttachment AS AMA ON ATT.AttachmentId = AMA.AttachmentId
INNER JOIN Email AS EML ON EML.ActivityId = AMA.ObjectId
INNER JOIN ActivityPointer AS ACT ON EML.ActivityId = ACT.ActivityId
WHERE
AMA.ObjectTypeCode = 4202 — emails
AND ATT.FileSize > 50000
AND ACT.ModifiedOn < GETDATE() – 730 — older than 2 years
order by ATT.FileSize desc — to process larger attachments first
)
SET @Count= @@ROWCOUNT — effted rows
SET @Total = @Total + @Count
COMMIT TRANSACTION;
PRINT ‘Processed ‘ + CAST(@Total as varchar) + ‘ records.’
END

Her is the sql script to find out the top 10 tables with largest size:

select top 10 schema_name(tab.schema_id) + ‘.’ + tab.name as [table],
cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
from sys.tables tab
join sys.indexes ind
on tab.object_id = ind.object_id
join sys.partitions part
on ind.object_id = part.object_id and ind.index_id = part.index_id
join sys.allocation_units spc
on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + ‘.’ + tab.name
order by sum(spc.used_pages) desc;

Source: https://dataedo.com/kb/query/sql-server/list-10-largest-tables

Javed Iqbal

Dynamics 365 (CRM) Users, Security Roles and Assign/Delete Views

/i , /af

Dynamics 365 CRM gives out of the box functionality to view a user’s security role but what if we want to view all users who have a particular security role? we might have to build query using advanced find or something else…
Another situation can be if a user have left company but owns som important views. How to share those to other(s) or delete them?
To answer these questions we wrote a little utility that will help:
1. View a user’s security roles.
2. View users a particular security role is assigned to.
3. View a user’s personal views
4. Delete a user’s personal views
5. Assign a user’s views to another user (keeping both source user and target user updated instantly)
6. Swap between source and target user updating their views windows.

To install simply download the managed solution from github DownloadLink and import to organization v9.1 and open configuration page.

Below is a screenshot of utility.

Javed Iqbal

Scribe Insight “Error opening data object ” (solved). Security update for microsoft windows (KB4512517)

/i , /af

If anyone is experiencing this error then there can be one of the following 2 reasons.
(If error just started showing up without any change and integration has been working fine before then jump straight to option 2)

  1. Rights issue
    – Check accound running scribe services have permissions to collaborations folder and Message queues
    – Check scribeinternal dns or file dns is of type system dns not user dns
  2. Security update for microsoft windows (KB4512517)
    If integration has been working fine and error showed up then there might be this security update installed. You can uninstall the update or contact scribe for other solution.
Javed Iqbal

BUTTON(RIBBON) TO CALL PLUGIN (PASS PARAMETERS AND RECEIVE RESULTS)

/i /af

At some point on the roadmap of every Dynamics 365 (CRM) developer’s life, one must have wished to press a button to call a Plugin.
A combination of Custom Actions introduced in CRM 2013 and webapi introduced in CRM 2016 made this wish very easy. Developers have used different techniques to achieve the same goal e.g. creating records of dummy enttities or updating dummy fields of an entity to call plugins.
There is a smarter way to do so using Custom Actions and here is how we can do it:
Our solution consists of 4 main parts:

1. A CRM custom action with input/output parameters (it can be bound(on an entity) or unbound(global) depending on your requirements)
2. A plugin registered on the custom action (this will perform the task and will set the outpur parameter with results)
3. Script under the button: this will use webapi to call custom action sending input parameters if necessary and will receive the results as output paramters
4. Ribbon button to call the javascript function

Læs mere

Javed Iqbal

USING CRM ACTIONS TO OVERCOME WORKFLOW LIMITATION OF ACCESS TO MAIN OR RELATED ENTITIES

/i /af

Limitation:
One of the workflow limitations is; it does not support access to its next related entity. One can only access the main entity or the related entities but not next related entity.

Example:
we have 3 entities Account, Contact and CustomEntityA.
Account is related to Contact and Contact is related to CustomEntityA. Account is not directly related to CustomEntityA.

If we want to update a field e.g. description on CustomEntityA on update of the description of Account, or if we want to update Account’s description from CustomEntityA’s description, we cannot do it with OOB workflow. We can either write plugin/customWorkflowActivity or there is another option without coding anything which is combination of Action and Workflow.
Læs mere