In one of the on going projects we had to create considerable amount of SSIS packages to upload and generate various files. Some times we encounter a scenario where the Control Flow view gets corrupted. All the packages were in Source Control with TFS2010. Couldn’t find the exact reason for this. But found a way to correct the issues. Following is a sample image of the corrupted Control flow of the package.
Because of this issue we can’t do further amendments to the control flow as it is not responding at this stage. At one stage it appears that we had to create the package from the scratch. It is a nightmare to do so for a complex ETL package. I found a workaround to overcome this issue and the source code being checked in to the Source Control helped me a lot. Following are the steps to resolve the problem.
I was using Powershell scripting to create automated deployment scripts for SSIS packages and believe me even though Powershell is a great scripting language it was quite difficult get a script syntactically correct and error free for first time. To overcome this difficulty I used the Powershell ISE (Integrated Scripting Environment). It gives some form of aid to my scripting needs, but I have to say not much. As my self and the team were battling with tight sprint dead lines, these automated scripts writing took considerable amount of time. This was affecting the completion of sprints and one reason for increasing the remaining work at the end of sprints. It was a nightmare to manage the scripts scattered with the team.
When I was reading through my emails I got this email from Tech Republic, of course I’m tech republic fan, regarding this Powershell extension for Visual Studio 2010. So I read it and got the components installed. First you need to install the PowerGUI, otherwise the Visual Studio extension will not work.
I was looking forward to see the SSIS development with BIDS 2010, because of the so many problems that I had while implementing SSIS packages with BIDS 2008. So I finally had a chance to look at it with tech net virtual labs. Voila, an improved version of BIDS with VS 2010.
One of the CRM 2011 projects that I’m currently working on required several documents to be imported on daily basis. These documents were required by the payments processes and some other membership related processes.
So we have been given the opportunity to use SQL Server Integration Services (SSIS) to load these files to CRM for processing. But the problem came with how we could load the data to CRM. To overcome this barrier, CozyRoc SSIS tools became very handy (Many thanks to our lead architect Huw Edmunds for introducing this). For more information visit http://www.cozyroc.com/
So my self did lots of R&D on these tools to get it work with CRM as there is very little documentation support over these, but we got great deal of application support from CozyRock.
When I was designing these SSIS packages using the BIDS 2008 I came across situations where I have to undo things. But surprisingly its not possible. For example if you’d accidently delete any of the workflow activities it cannot be undone. Even the undo and redo buttons are disabled on tool bars.
But came to know that this is one of the new features available with SQL Server ‘Denali’ BIDS. Looking forward to use this.