Posted in Data Migration, Dynamics 365, Dynamics CRM, SSIS

Data Migration SSIS Tips – 1

Recently we were migrating records from Source system to staging database and then to Dynamics CRM 2016 using SSIS.

Few issues are encountered while we migrating the large number of records.

Script component:

Some time we might require to use the script components because some conversion is not possible/not easy with SQL. In this case, number of columns which you processing will affect performance of the package.

How to improve ?

Avoid using the script component as much as possible

We can utilise the BDD – Balanced Data Distributor. It helps to parallelize the data flow. if you new to this, here is the link for more information (BDD – Balanced Data Distributor)

Try to use synchronised transformation. It’s better compared to Asynchronous. Ideally it’s up to the requirement. for better understanding, here is the link (Synchronous vs Asynchronous

Derived column:

The number of columns in derived columns also affects the performance of the package.

Then how to ?

Try not to use the derived columns as much as possible

If there is a need, we have to use. then use multiple “Derived columns” component for manipulation. Instead of doing all the manipulation in single “Derived column”. Significantly it will improve the performance

Data Conversion:

This component in SSIS which will help to convert the data type of column to another compatible data type. It’s just drag and  drop and easy to use. when you really care about the performance, then here we go. It does maintains the old column as well as converted column too.

Then how to ?

Try to do the conversion at source itself. It’s best as package don’t need to maintain the duplicate of it.

If the conversion is not possible at source, do the manipulation at staging.

Always, i prefer to have the manipulation in Stored Procedure. It really faster and it will not impact the performance as much as others.

Do share your knowledge on these..

Keep Sharing …

Advertisements
Posted in Data Migration, SSIS

Things to consider “Package part” for your implementation 

What’s best we got ? 

  • We can create a template which will be reused forever
  • Do a change in one place,  it will reflect wherever it’s referred
  • Reduces developer time in finding the copy paste issues LoL
  • Able to pass parameters to and fro
  • Best work for the control flow component which will be independent of the previous steps ex. Package audit
  • Defined connection managers in package parts will be carried out wherever it’s used.  I guess it’s best thing. So many times I have faced this issue during copy paste. Hope this issue will not repeat if we use package parts

What to be considered ? 

  • The configured package parts variables in your package will be overridden , whenever you modifying / refreshing the package parts 
  • Best works for the control flow executables which will be independent of the previous steps ex. Package audit
  • You can debug package part from the package by keeping breakpoints in package parts
  • Variables in package parts are Task Scoped.  So you have to think on how to pass values to it
  • Using expression editor in package parts variable,  we can pass values 
  • Passing object variables to package parts is not possible because expression editor will not support object variables 

This feature is available from SQL server 2016. Since this is first release we can expect more in upcoming releases.

Try it out and share with your thoughts . 

Happy blogging . . .