This is a quick guide to the simplest form of SSIS import. I will not cover all of the amazing ETL possibilities you can do with SSIS, because I am not the correct person to set this up:)
The background for this post is also trying to learn the differences between DataFlow and SSIS in regards to API stats online.
The setup
- Install BIDS LINK HERE – If you don’t have visual studio
- Install KingswaySoft LINK HERE
- Get Excel File with contacts (94,883)
The structure is very simple here. Get file, add one data column and then import to dynamics.
The flat file I am getting is a simple .csv file and you see the following columns.
Next step is a bit odd, but that’s how lookups work in SSIS. I have to create a new column that is called “account”, because i have to define the lookup type. This Derived Column does nothing else.
In the end we connect to Dynamics via the KingswaySoft connector.
I have chosen to use alternate keys. If you wonder what this is, look at my previous post about alternate keys and DataFlow.
Then I click on columns to map the fields
For the lookup we connect to ParentCustomer. Once you have mapped the field, i can choose to what field to match on the Account. I open the “…” and here I choose the primary key for account as matching. You could use whatever you want, but I have created alternate key for account on AccoundNumber.
The result
I ran this test during the night time at the same time as DataFlow. SSIS used 1 hour and 22 minutes for the exact same file, and that is fine by me. There are so many different variables in online load times, that 20 minutes doesn’t really prove anything.
What about the API?
I wrote a post about DataFlow and API. Mostly because I was curious about the analytics within CDS. I don’t understand the numbers i see, so I have written to Support about the numbers. What I heard back was because of the batch of records being entered, this should be about right…
If this is correct, SSIS is a lot more efficient in the API calls to Dynamics, but took more time in this one single test i performed.
SSIS = 3,501 API Calls
DataFlow = 13,544 API Calls
CRM Keeper 
One reason why it was slower would be that you don’t seem to have used multithreaded writing – that could easily have halved the time it took to process the files but there is a very fine art in getting Kingswaysoft batch parameters correct, different entities will work best with batch sizes and different thread counts.
LikeLike
I wasn’t sure how online would like the multithread part, and I don’t know enough about ssis I guess 😉
Tnx for tip
LikeLike
This is what I found when trying different batch and thread combination https://nishantrana.me/2018/06/04/optimum-batch-size-while-using-ssis-integration-toolkit-for-microsoft-dynamics-365/
LikeLike