How to improve the performance in merge component on ssis

we need to merge some records with 30M records and it's not finished the SSIS and PC was also hanging. 

How will we improve the slowness?

While its executing, from the design, where do you see the execution getting stuck? Is it at the Source, transformation or at the Merge Join component? You cold also check the execution results to see if there are any warnings about memory related issues. If it’s a buffer or memory related issue, then usually it should throw warnings in the execution report. You could try and check that. Also, you could try working with “AutoAdjustBuffer” option in SSIS data flow properties to see if that makes a difference. And this might be related to system memory, and you could try deploying in any available test server and see if this is resolved (provided that memory requirements are met).

It looks like the delay is happening at the out of the box transformation components. However, could you try using our Premium Lookup component instead of the sort and merge join? You could do a similar join design using the Premium Lookup component. You can find the component in the SSIS Toolbox as we can see that you are already using our SSIS Productivity Pack. Please find the Online Help Manual below.

• https://www.kingswaysoft.com/products/ssis-productivity-pack/help-manual/premium-data-flow-components/premium-lookup

 

How to filter on the Time part of a date in FetchXML using KingswaysoftSource

we need to retrieve the records from defined time every day.

Ideally the format that is acceptable by the API would need to be provided. You could identify this by checking the actual response from the API using a proxy like Fiddler. So in this case, you could give either of the format and the expression as shown below.

How to pass two variables on forloop container on ssis

 

The steps for setting up a foreach loop container would involve the following logic.

1. Create an SSIS variable of type Object (named RecordCollection for example) and create another String variable.
2. Then In your First Data flow Task

1. Retrieve your records from your Source component.
2. Attach the output of the Source component to the out-of-the-box RecordSet Destination component and set the VariableName to User:: RecordCollection in the RecordSet Destination component properties

3. In your Control flow, Create a Foreach loop container and link your previous data flow task to the container.
4. In the Collection page of the Foreach Loop Editor, choose Foreach ADO Enumerator and select User:: RecordCollection as the ADO object source variable.

Then in the Variable Mappings page select the string variable you have created to its respective Index and click OK. The Index start from 0 and it is the location of the column in the RecordSet.

For Reference

https://bageshkumarbagi-msbi.blogspot.com/2016/04/recordset-destination-in-ssis.html#:~:text=After%20the%20RecordSet%20destination%20saves,into%20a%20separate%20package%20variable.

How to Extract the Audit Logs for Multiple Dynamics CRM Entities

 Extracting Audit Logs for Multiple Dynamics CRM Entities

Two methods are there for extract the all details of audilog using KingswaySoft components

Method 1: Source Entity as Audit

Source Entity as Audit will give the output audit records for all entity records with the respective actions performed and their corresponding details

Method 2: Source Type as Audit Logs

The second method, the more detail-oriented method, is to choose the Source Type as Audit Logs in the CRM Source component, and it requires a FetchXML query.

For method2, we need to install the kingswaysoft components toolkit again using “complete” option – This option is important.

A Foreach Loop Container can be used to iterate through each entity name, and thus get the audit logs for all the entities. The design could be as below:

The first data flow task will write the entity names to a variable

The Foreach Loop Container can be used to get the variable values into the CRM Source component. The FetchXML section can use variables directly, and the below query can be used:

Field “createdon” in the auditlogs entity stores the datetime of when the master record in the respective entity was first created in the CRM system, it is not the datetime when this audit log record was created. If you want to use such a datetime created filter on audit records, the right approach would be to work with Audit entity. You could also try “Source Type” as FetchXML on audit entity as below screen shot, which the “createdon” field is the audit record created datetime.

For reference

http://www.kingswaysoft.com/blog/2019/10/16/Extracting-Audit-Logs-for-Multiple-CRM-Entities

Handling Microsoft.IdentityModel assembly error

How to reslove the Microsoft.IdentityModel assembly error

 While Retrieve the auditlog from contact, it's going to failed. 

because of following reason.

 

[Audtlog [167]] Error: An error occurred with the following error message: "System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.IdentityModel, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified. (SSIS Integration Toolkit for Microsoft Dynamics 365, v11.1.0.7311 - DtsDebugHost, v15.0.1301.433)".

 

 

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Audtlog returned error code 0xC02090F5.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

The error that you are receiving should be an installation related issue. Please try the following and let us know if this does not solve the issue.

1. Close all Visual Studio SSDT packages and windows.
2. Uninstall our Dynamics components from control panel.
3. Install the same toolkit again using “complete” option – This option is important.

 

How to write TOP 1 in FetchXML query

How to write TOP 1 in FetchXML query

 

FetchXML query works differently from SQL, if you need top 1, then you should write like something below.

<fetch mapping="logical" count="1" page="1" version="1.0">

  <entity name="account">

    <all-attributes />

  </entity>

</fetch>

 

Basically you need the following instead of top attribute.

count="1" page="1"

 

How to pass two variables on forloop container on SSIS?

How to pass two variables on forloop container on SSIS?

The steps for setting up a foreach loop container would involve the following logic.

1. Create an SSIS variable of type Object (named RecordCollection for example) and create another String variable.
2. Then In your First Data flow Task

1. Retrieve your records from your Source component.
2. Attach the output of the Source component to the out-of-the-box RecordSet Destination component and set the VariableName to User:: RecordCollection in the RecordSet Destination component properties

3. In your Control flow, Create a Foreach loop container and link your previous data flow task to the container.
4. In the Collection page of the Foreach Loop Editor, choose Foreach ADO Enumerator and select User:: RecordCollection as the ADO object source variable.

Then in the Variable Mappings page select the string variable you have created to its respective Index and click OK. The Index start from 0 and it is the location of the column in the RecordSet.

 

You can refer to this article to know more details about how your package design should be.

Update the timezone for users on dynamics 365

we can able to update the timezone using user setting utility package on xrmtoolbox

select the user then choose the correct timezone like that below image

How to schedule the bat file on sql server

Need to open notepad and type it like this below then save it as .bat extension

"C:\\Program Files (x86)\\Microsoft SQL Server\\140\\DTS\\Binn\\DTExec.exe"  /f "<<DIR>>\\SSISPackageName.dtsx"

We can run batch file through ssis job

• Open the visual studio
• Create a new package in SSIS
• You will start with the Control Flow open
• Drag and Drop an Execute Process Task from the Toolbox 

make sure you have both “SQL Server Integration Services″ and “SQL Server Agent (MSSQLSERVER)” services running on your server. 

Open up Microsoft SQL Server Management Studio and connect to your server.

Expand SQL Server Agent.  Right click Jobs and select New Job.  The following window will appear.

How to create the user on sql server SSMS

Before you add the user to the db, add the user to the server -- under the server, right click the security folder, and try it that way. Then you can grant permissions to the appropriate databases to that user

1.Create the user on server
CREATE LOGIN senthan WITH password='XXXXXXX';
CREATE USER senthan FROM LOGIN senthan;

2.create the user on database with read only permission
CREATE USER [senthan] FOR LOGIN [senthan] WITH DEFAULT_SCHEMA=[dbo]
GO

EXEC sp_addrolemember 'db_datareader', 'senthan';

3.Grant the Create Procedure permission to that user
GRANT CONTROL  ON SCHEMA :: dbo TO senthan WITH GRANT OPTION;
grant create procedure to senthan

How to check which version or edition of Microsoft® SQL Server is installed on windows

• Open the SQl server configuration

• click properties and go to advanced

Stock Keeping Unit Name will be the edition of SQL. Compare the displayed Version to the list below to find the version and service pack.

So we installed 2017 sql server based on below information

• SQL Server 2008:

1. SQL Server 2008 Service Pack 4 (10.00.6000.29)
2. SQL Server 2008 Service Pack 3 (10.00.5500.00)
3. SQL Server 2008 Service Pack 2 (10.00.4000.00)

• SQL Server 2008 Service Pack 1 (10.00.2531.00)

• SQL Server 2008 RTM (10.00.1600.22)

• SQL Server 2008 R2:

1. SQL Server 2008 R2 Service Pack 3 (10.50.6000.34)
2. SQL Server 2008 R2 Service Pack 2 (10.50.4000.0)
3. SQL Server 2008 R2 Service Pack 1 (10.50.2500.0)
4. SQL Server 2008 R2 RTM (10.50.1600.1)

• SQL Server 2012:

1. SQL Server 2012 Service Pack 2 (11.0.5058.0)
2. SQL Server 2012 Service Pack 1 (11.00.3000.00)
3. SQL Server 2012 RTM (11.00.2100.60)

• SQL Server 2014:

1. SQL Server 2014 Service Pack 1 (12.0.4100.1)
2. SQL Server 2014 RTM (12.0.2000.80)

• SQL Server 2016:

1. SQL Server 2016 SP2 (13.0.5026.0 – April 2018)    
2. SQL Server 2016 SP2 (13.0.5233.0 – November 2018)
3. SQL Server 2016 SP1 (13.0.4541.0 – November 2018)
4. SQL Server 2016 RTM (13.0.2216.0 – November 2017)

• SQL Server 2017:

1. SQL Server 2017 (14.0.3045.24 – October 2018)

How to handle the "remote: HTTP Basic: Access denied fatal: Authentication failed for " error while do the git clone

we need to update the correct config for git then we can able to avoid this issue("remote: HTTP Basic: Access denied fatal: Authentication failed for 'https://gitlab.com/myname/myproject'")

We need to follow the command to update the new password. 

git config --system --unset credential.helper

then enter new password for Git remote server.

Basic Information for PostgreSQL

Here i'm expaining basic command for postgreSQL

• Connect PostgreSQL Database server on centos

          sudo -i -u postgres

• Connect the Database 

           psql

• Show the users

           \d

• Connect the Database

           \c DBNAME

• Show the list of tables

           \dt

• Show the list of Databases

           \l

• Exit the Database Server Connection

          exit

• Exit the Database Connection

          \q

Connect PostgreSQL using SSIS on windows

Here i'm expaing how to connect postgreSQL using SSIS on windows below 

If we want to connect PostgreSQL on windows, we need to install driver(32 bit)

• Download and Install PostgreSQL Driver for ODBC(https://www.postgresql.org/ftp/odbc/versions/msi/)

• After install, we can create the datasource on ODBC 

• Open the Visual Studio and create the ODBC Source Connection

• Click New and create the connection

•