Using SSIS for performing data migration is lot easier than using a custom application to do the same. I recently had a opportunity to perform data migration using Cozyroc SSIS connector. Being new to Cozyroc and SSIS, I had to google a lot to find answers to my questions. The input files that need to be upserted into CRM, were split into multiple files with the same structure.
I refered this post (http://bi-polar23.blogspot.com.au/2007/08/loading-multiple-excel-files-with-ssis.html) on how to setup a foreach loop to read the excel files. I have a 64 bit version of Office 2013 installed, but to read XLSX files, you have to install Access 2010 32 bit database engine, because VS2012 is 32 bit. Installation is successful, but after that point my 64 bit Office goes into an "Activate Office" loop, even though I can run the SSIS package in VS2012. I am guessing this is becuase I have a Office365 subscription and installing 32 bit Acess 2010 confuses the activation mechanism.
So, I decided to use the Excel Source Plus component that comes with Cozyroc. Despite of properly following the instruction from the post, I found that only first file is read multiple times, which was not the behaviour I expected. After much experimentation, I found that setting the RetainSameConnection setting to false, fixes the issue and all the files are read in.
I hope this is useful to anyone doing data migration using Cozyroc.
I refered this post (http://bi-polar23.blogspot.com.au/2007/08/loading-multiple-excel-files-with-ssis.html) on how to setup a foreach loop to read the excel files. I have a 64 bit version of Office 2013 installed, but to read XLSX files, you have to install Access 2010 32 bit database engine, because VS2012 is 32 bit. Installation is successful, but after that point my 64 bit Office goes into an "Activate Office" loop, even though I can run the SSIS package in VS2012. I am guessing this is becuase I have a Office365 subscription and installing 32 bit Acess 2010 confuses the activation mechanism.
So, I decided to use the Excel Source Plus component that comes with Cozyroc. Despite of properly following the instruction from the post, I found that only first file is read multiple times, which was not the behaviour I expected. After much experimentation, I found that setting the RetainSameConnection setting to false, fixes the issue and all the files are read in.
I hope this is useful to anyone doing data migration using Cozyroc.
