tag:blogger.com,1999:blog-5472436831307966815.post1181564729617278388..comments2024-03-20T00:30:07.883-06:00Comments on Daniel Cai's Blog: Improve CRM Data Load Performance by using BDD in SSISDaniel Caihttp://www.blogger.com/profile/05461949321689249420noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-5472436831307966815.post-36863590835111252752015-04-13T10:37:27.429-06:002015-04-13T10:37:27.429-06:00Hi Richard,
Thanks for reaching out.
Unfortuna...Hi Richard, <br /><br />Thanks for reaching out. <br /><br />Unfortunately, I didn't keep the data flow tasks, and it should be a straightforward process to create them. For sample data, I used <a href="http://sourceforge.net/projects/spawner/" rel="nofollow">Spawner Data Generator</a> which allows you to generate any number of sample records based on the schema you define. <br /><br />Let me know if you have any additional questions. <br /><br />Kind regards,<br />Daniel Cai Daniel Caihttps://www.blogger.com/profile/05461949321689249420noreply@blogger.comtag:blogger.com,1999:blog-5472436831307966815.post-62093632337161361532015-04-13T10:26:12.803-06:002015-04-13T10:26:12.803-06:00Dear Daniel,
I hope this finds you well.
Great w...Dear Daniel,<br /><br />I hope this finds you well.<br /><br />Great work on integration and migration of data.<br /><br />Kindly requesting for the data flow tasks or sample data, and SSIS package that used in your awesome blog titled " data flow tasks or sample data, please feel free to let me know, so that I can send you the SSIS package."<br /><br /> Your assistance will be greatly appreciated<br /><br />Regards<br /><br />Richard SillaAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5472436831307966815.post-58355252671977892682014-06-17T07:56:44.771-06:002014-06-17T07:56:44.771-06:00@John, we handle many details that CozyRoc doesn&#...@John, we handle many details that CozyRoc doesn't handle. If you have another project engagement, I will be more than happy to work with you for a case study or something similar. Please feel free to ping me at any time. <br /><br />Thanks,<br />Daniel Cai | http://www.kingswaysoft.comDaniel Caihttps://www.blogger.com/profile/05461949321689249420noreply@blogger.comtag:blogger.com,1999:blog-5472436831307966815.post-36349819676827047012014-06-17T07:49:34.432-06:002014-06-17T07:49:34.432-06:00@John, that's a very impressive number. With C...@John, that's a very impressive number. With CozyRoc, you don't really have a way to specifically point to a physical server, so you have to use the hosts file hack, with us, there is no need to do so. Just trying to point out a minor difference.Daniel Caihttps://www.blogger.com/profile/05461949321689249420noreply@blogger.comtag:blogger.com,1999:blog-5472436831307966815.post-82857568057204521352014-06-17T07:37:05.433-06:002014-06-17T07:37:05.433-06:00Update, we are now using 8 SSIS servers, with the ...Update, we are now using 8 SSIS servers, with the CRM Front end installed on all of them, and the host files having SSIS point to itself and a 20 processor db server. Each of the ssis/app combos have 16 procs. We have seen minutes of over 180,000 peek and are averaging about 155,000 contacts per minute (remember, this is really 1 base row, 1 extensionbase, 2 address base, and 2 address extensionbase records, so 155,000 x 6 or 930,000 physical rows per minute) avg = 2,583 contacts a secondAnonymoushttps://www.blogger.com/profile/14709850990977101523noreply@blogger.comtag:blogger.com,1999:blog-5472436831307966815.post-70826761834679404012014-03-05T07:34:02.696-07:002014-03-05T07:34:02.696-07:00Ah, yes sorry about that, clients choice. We are a...Ah, yes sorry about that, clients choice. We are an MS Partner, using the tools they have. P.S. In tweaking the heck out of everything we have been able to hit 500 contacts per second, about 30k a minute, with turns out to about 38 minutes per million rows give or take. Fun times.Anonymoushttps://www.blogger.com/profile/14709850990977101523noreply@blogger.comtag:blogger.com,1999:blog-5472436831307966815.post-3472421449981772272014-01-23T14:29:02.195-07:002014-01-23T14:29:02.195-07:00@John, I am offended when you mention CozyRoc. Jus...@John, I am offended when you mention CozyRoc. Just kidding, I am not. Hopefully this post was helpful, thanks for sharing your experience. <br /><br />Kind regards,<br />Daniel Cai | <a href="http://www.kingswaysoft.com" rel="nofollow">http://www.kingswaysoft.com</a>Daniel Caihttps://www.blogger.com/profile/05461949321689249420noreply@blogger.comtag:blogger.com,1999:blog-5472436831307966815.post-91845302307905197522014-01-23T13:19:02.697-07:002014-01-23T13:19:02.697-07:00I tried all of this using CozyRoc as my adapter of...I tried all of this using CozyRoc as my adapter of choice, and while using BDD and the setting changes recommended by you and others, it appears that with 5 or 8 destination components, the throughput to 2 network load balanced web servers was roughly the same, about 7k records per minute (give or take). I could not get it to go past that limit no matter what changes I made to buffers etc. I instead decided to leave my 8 BDD components in one task inside one Control Flow item, and then copy that control flow item 5 times. With this, and altering the source in each control flow item to break it out, I was able increase this to a whopping 18k per minute, which equated to about 1 million contact records per 55 minutes. This maxed out the SSIS server (10GB RAM, 4 Processors) nearly (could probably add one more), and made the 2 Load Balanced servers peak at 95% usage. I believe I've reached my limit with this hardware configuration.Anonymoushttps://www.blogger.com/profile/14709850990977101523noreply@blogger.comtag:blogger.com,1999:blog-5472436831307966815.post-83617210780469821322013-10-29T11:53:56.865-06:002013-10-29T11:53:56.865-06:00You would need to make changes to a few .config fi...You would need to make changes to a few .config files. In the case that you are running on a 64-bit system, you would need to make changes to the files under both Program Files and Program Files (x86) folders. Daniel Caihttps://www.blogger.com/profile/05461949321689249420noreply@blogger.comtag:blogger.com,1999:blog-5472436831307966815.post-86291476800261746482013-10-29T11:42:50.670-06:002013-10-29T11:42:50.670-06:00I'm still finding the way to implement multi t...I'm still finding the way to implement multi thread, don't know why the Sql Server 2012 no catch the configuration that you indicate for the XML files configuration:<br /> <br /> <br /> <br /> <br /> <br />Geov@nnyhttps://www.blogger.com/profile/01864806536484856329noreply@blogger.comtag:blogger.com,1999:blog-5472436831307966815.post-29709149390739411002013-10-29T11:33:28.961-06:002013-10-29T11:33:28.961-06:00If you don't mind using Conditional Split, it ...If you don't mind using Conditional Split, it should still work for SQL Server 2012. Daniel Caihttps://www.blogger.com/profile/05461949321689249420noreply@blogger.comtag:blogger.com,1999:blog-5472436831307966815.post-9108328954919057002013-10-29T11:31:45.489-06:002013-10-29T11:31:45.489-06:00Yes, but I have a DTS that works very good in SQL ...Yes, but I have a DTS that works very good in SQL Server 2008 RA, OS Windows Server 2008 R2 using a conditional split for simulate multi thread I can up the data to Dynamics CRM 2011 without problems, but now I'm using OS Windows Server 2012 and Sql Server 2012 and no works the multi thread, just use 2 current connections to the web servicesGeov@nnyhttps://www.blogger.com/profile/01864806536484856329noreply@blogger.comtag:blogger.com,1999:blog-5472436831307966815.post-12782386590109378062013-10-29T10:48:11.939-06:002013-10-29T10:48:11.939-06:00Yes, you can. There is a new version of BDD compon...Yes, you can. There is a new version of BDD component which supports SQL Server 2012. Daniel Caihttps://www.blogger.com/profile/05461949321689249420noreply@blogger.comtag:blogger.com,1999:blog-5472436831307966815.post-12338883315124380622013-10-29T10:47:12.734-06:002013-10-29T10:47:12.734-06:00Can I do the same for Sql Server 2012?Can I do the same for Sql Server 2012?Geov@nnyhttps://www.blogger.com/profile/01864806536484856329noreply@blogger.com