IC746386.jpg' alt='How To Install Gacutil On Windows Server 2012' title='How To Install Gacutil On Windows Server 2012' />Integrating CRM using SQL Integration Services SSIS Microsoft Dynamics 3. Team blog. Today we welcome our guest blogger CRM MVP Darren Liu from the Crowe company. YLtLMUmYfN4/UoWmA-ljDpI/AAAAAAAAAQA/Ph6LPY4hoaU/s1600/Capture2.PNG' alt='How To Install Gacutil On Windows Server 2012' title='How To Install Gacutil On Windows Server 2012' />Many Microsoft Dynamics CRM MSCRM implementations involved integration with other systems. When I think of integrations between systems, the common approaches pop up immediately in my mind are Biz. Talk, Scribe or to write a custom integration framework using the CRM Software Development Kit SDK. By working with Darren Hubert, Solution Architect from the Microsoft National Service Pursuit Team in my last project, I have discovered another approach that I encourage you to consider in the your integration project which involves using SQL Integration Services SSIS to integrate with Microsoft Dynamics CRM. SSIS is a platform for building data integration solutions and it is a service that runs on the SQL Server. SSIS replaces the existing Data Transformation Services DTS, which was introduced to the market as a component of SQL Server 7. ETL Extraction Transformation Load process. Because SSIS in SQL 2. Web Services by default, I did not spend much time into my research for my previous integration projects. Experts Exchange Questions How do I install Microsoft. SqlServer. Dmf assembly into the Global Assembly Cache How to add a Custom SSIS Component to the SSIS Tool Box in Visual Studio, so you can use it in your Control or Data Flows. How to Install a DLL to the GAC on Windows Server 2012 Using Only PowerShell. Should I remove Microsoft Report Viewer Redistributable 2008 SP1 by Microsoft Microsoft Report Viewer control enables applications that run on the. Thanks again to Darren Hubert and his friends, who showed me a work around using a CRM proxy class in my last project. I was able to successfully integrate CRM 4. SSIS. Here I would like to share with all of you on what I have learned so that you can leverage it on your next CRM integration projects. Before we get started, heres the list of requirements. SQL Server 2. 00. StandardEnterprise Edition with SQL Integration Service Installed. Microsoft Dynamics CRM 3. Visual Studio 2. 00. Professional Edition. CRM SDK, C and VB. Net knowledge In this blog, I will use a simple example to show you how to send contact data stored in a SQL database to MSCRM 4. PDF files that contain the Visual Studio 2005 documentation. I have created an integration pack with C for System Center Orchestrator 2012 that runs on Windows Server 2012 R2 that depends on Newtonsoft. Json which is. CRM Web Services using SSIS. Source Data. The source data is from the data from the other system that you would like to send to the CRM system. You source data can be a text file, an Access database, an Oracle database, etc In this example, I will create a simple Contacts table in a SQL database thats already existed in environment. NET Interview Questions and Answers for Beginners consists of the most frequently asked questions in. NET. This list of 100 questions and answers gauge your. I am using Windows 7 Enterprise 32 bit. I have used Windows command line, and also used VSTS 2008 command line, but when executing gacutil. Source Table Contacts 1 CREATE TABLE dbo. Contacts 2 Contact. Id int IDENTITY1,1 NOT NULL, 3 First. Name varchar5. NULL, 4 Last. Name varchar5. NULL, 5 Phone varchar5. NULL, 6 Email varchar5. NULL, 7 CONSTRAINT PKContacts PRIMARY KEY CLUSTERED 8 9 Contact. Id ASC 1. 0 WITH PADINDEX OFF, STATISTICSNORECOMPUTE OFF, IGNOREDUPKEY OFF, ALLOWROWLOCKS ON, ALLOWPAGELOCKS ON ON PRIMARY 1. ON PRIMARY 1. 2 GO 1. INSERT INTO Contacts First. Name, Last. Name, Phone, Email 1. VALUES John,Smith,3. INSERT INTO Contacts First. Name, Last. Name, Phone, Email 1. VALUES Darren,Liu,3. INSERT INTO Contacts First. Name, Last. Name, Phone, Email 2. VALUES Adam,Johnson,3. GOCreate CRM Proxy Class. The SSIS framework provides a Web Service Task which executes a Web Service method, however its difficult to use. To reduce the complexity of the SSIS integration with CRM, generate a CRM Proxy class using Visual Studio. This will make the integration process much smoother and you will encounter less road blocks. Start a New C Class Library Project. Create a project name CRM. ProxySign the Project. Right click on the project and select Properties. Click on Signing tab and check the Sign the assembly checkbox. Select lt New from Choose a strong name key file dropdown. Give it a name for the Key. In this example, I use integration as my key name. Add CRM Web Services. Add CRM Web Service and CRM Discovery Service to the CRM. Proxy project. Visual Studio will automatically run WSDL. CRM Web Services which we will use later on in building the SSIS package. Right click on Crm. Proxy project in the solution pane, then select Add Web Reference. In the URL text box, type in the CRM Web Service URL and give Crm. Sdk as the Web reference name. My CRM Web Service URL in this example is http localhost 5. Crm. Service. asmx. Compile the Crm. Proxy Project in Release Mode. Deploy CRM Proxy Class to SSISIn order to use the Crm. Proxy library in our SSIS package, we need to GAC the Crm. Proxy. dll and also copy the Crm. Proxy. dll to the. Net Framework 2. 0 folder. The default location of the. Net Framework folder is C WindowsMicrosoft. NetFrameworkv. 2. To GAC the Crm. Proxy. C Windowsassembly folder. Create SSIS Package. Start a New Integration Service Project. After creating the project, follow the steps below to setup the SSIS package. Rename Package. dtsx to Contact. Right click on Data Source in the Solution Pane and then select New Data Source. Follow the instruction on the wizard to create a database connection to the database contains your source data. In this example, I named my data source CRMDB. Right click on Connection Managers pane to add a New Connection From Data Source. Add Control Flow Items. Drag and drop Data Flow Task from the Toolbox to the Control Flow Design Pane. Add Data Flow Items. Double click on the Data Flow Task item and it will take you the Data Flow Design Pane. In here we will specify the source data and also write script to send data to CRM. Specify Source Data. Drag and drop OLE DB Data Source to the design pane. Double click OLE DB Source to open up the OLE DB Source Editor. Select CRMDB from the OLE DB Connection Manager drop down box. Select Table or view from Data Access Mode drop down box. Select Contacts from Name of the table or the view drop down box. Click OK. Setup Script Component. Drag and drop Script Component to the design pane. Select Transformation and then click OK. Double click the script component to open up the Script Transformation Editor. Select the column that you would like to send to MSCRM from the Input Column window. In this example, I selected First. Name, Last. Name, Phone and Email. Remove Output in the Inputs and Outputs section since we are not output anything in this example. Click on the Script tab, click on Design Script button. Visual Studio for Applications windows should open. Add CRM Proxy Class to SSISSince we generated and GAC the Crm. Proxy library in the step above, we will add a reference to the proxy class in this step. Right click on Reference and select Add Reference. Select Crm. Proxy from the list of the. Net components and then click Add. Coding the Package. In order to complete the script, we also need to add the following reference System. Web. dll, System. Web. Services. dll and System. Xml. dll. Then add the following imports statement Imports Crm. Proxy. Crm. Sdk. Imports System. Xml. Imports System. Web. Services. Lastly, copy and paste the following code to the Script. Main section 1 Public Class Script. Main 2 Inherits User. Component 3 Dim Service As Crm. Service 4  5 Public Overrides Sub Input. Process. Input. RowBy. Val Row As Input. Buffer 6  7 Create Contact object 8 Dim contact As New contact 9  1. Row. First. Name 1. Row. Last. Name 1. Row. Phone 1. 3 contact. Row. Email 1. 4  1. Service. Createcontact 1. End Sub 1. 7  1. Public Overrides Sub Pre. Execute 1. 9 My. Base. Pre. Execute 2. Create CRM Service 2. Service New Crm. Service 2. Service. Credentials System. Net. Credential. Cache. Default. Credentials 2. Service. Url http localhost 5. MSCrm. Services2. Crm. Service. asmx 2. Dim token As New Crm. PDB Files What Every Developer Must Know. Thanks for visiting one of the most popular pieces Ive ever written Based on questions Ive written a few follow up articles you might find useful to extend your PDB knowledge after reading this one. Keeping Specific PDB Files from Loading in the Debugger. Do PDB Files Affect PerformanceCorrectly Creating Native C Release Build PDBs. How Many Secrets do. NET PDB Files Really Contain If youd love to know more about debugging, check out my Mastering. NET Debugging or Mastering Windows Debugging for native C courses over at our great on demand learning site Wintellect. NOW. Because youre obviously a great developer for reading this, use the code JOHNR 2. Are you more interested in instructor lead training We can do that to so check out our course catalog. If you have any questions about PDB files ask a question in the comments, email me john AT wintellect DOT com or ask me on Twitter John. Wintellect. Most developers realize that PDB files are something that help you debug, but thats about it. Dont feel bad if you dont know whats going on with PDB files because while there is documentation out there, its scattered around and much of it is for compiler and debugger writers. Activities To Develop Entrepreneurial Skills In Business. While its extremely cool and interesting to write compilers and debuggers, thats probably not your job. What I want to do here is to put in one place what everyone doing development on a Microsoft operating system has to know when it comes to PDB files. This information also applies to both native and managed developers, though I will mention a trick specific to managed developers. Ill start by talking about PDB file storage as well as the contents. Since the debugger uses the PDB files, Ill discuss exactly how the debugger finds the right PDB file for your binary. Finally, Ill talk about how the debugger looks for the source files when debugging and show you a favorite trick related to how the debugger finds source code. Before we jump in, I need to define two important terms. A build you do on your development machine is a private build. A build done on a build machine is a public build. This is an important distinction because debugging binaries you build locally is easy, it is always the public builds that cause problems. The most important thing all developers need to know PDB files are as important as source codeYes, thats red and bold on purpose. Ive been to countless companies to help them debug those bugs costing hundreds of thousands of dollars and nobody can find the PDB files for the build running on a production server. Without the matching PDB files you just made your debugging challenge nearly impossible. With a huge amount of effort, my fellow Wintellectuals and I can find the problems without the right PDB files, but it will save you a lot of money if you have the right PDB files in the first place. As. John Cunningham, the development manager for all things diagnostics on Visual Studio, said at the 2. PDC, Love, hold, and protect your PDBs. At a minimum, every development shop must set up a Symbol Server. Ive written about Symbol Servers in MSDN Magazine and more extensively in my book, Debugging. NET 2. 0 Applications. You can also read the Symbol Server documentation itself in the Debugging Tools for Windows help file. Look at those resources to learn more about the details. Briefly, a Symbol Server stores the PDBs and binaries for all your public builds. That way no matter what build someone reports a crash or problem, you have the exact matching PDB file for that public build the debugger can access. Both Visual Studio and Win. DBG know how to access Symbol Servers and if the binary is from a public build, the debugger will get the matching PDB file automatically. Most of you reading this will also need to do one preparatory step before putting your PDB files in the Symbol Server. That step is to run the Source Server tools across your public PDB files, which is called source indexing. The indexing embeds the version control commands to pull the exact source file used in that particular public build. Thus, when you are debugging that public build you never have to worry about finding the source file for that build. If youre a one or two person team, you can sometimes live without the Source Server step. For the rest of you, read my article in MSDN Magazine on Source Server to learn how to use it. The rest of this entry will assume you have set up Symbol Server and Source Server indexing. One good piece of news for those of you who will be using TFS 2. Build server will have the build task for Source Indexing and Symbol Server copying as part of your build. One complaint Ive heard against setting up a Symbol Server from some teams is that their software is too big and complex. I have to admit that when I hear people say that it translates to me as My team is dysfunctional. Theres no way your software is bigger and more complex than everything Microsoft does. They source index and store every single build of allproducts they ship into a Symbol Server. That means everything from Windows, to Office, to SQL, to Games and everything in between is stored in one central location. My guess is that Building 3. Redmond is nothing but SAN drives to hold all of those files and everyone in that building is there to support those SANs. Its so amazing to be able to debug anything inside Microsoft and you never have to worry about symbols or source provided you have appropriate rights to that source tree. With the key infrastructure discussion out of the way, let me turn to whats in a PDB and how the debugger finds them. The actual file format of a PDB file is a closely guarded secret but Microsoft provides APIs to return the data for debuggers. A native C PDB file contains quite a bit of information Public, private, and static function addresses. Global variable names and addresses. Parameter and local variable names and offsets where to find them on the stack. Type data consisting of class, structure, and data definitions. Frame Pointer Omission FPO data, which is the key to native stack walking on x. Source file names and their lines. A. NET PDB only contains two pieces of information, the source file names and their lines and the local variable names. All the other information is already in the. NET metadata so there is no need to duplicate the same information in a PDB file. When you load a module into the process address space, the debugger uses two pieces of information to find the matching PDB file. The first is obviously the name of the file. If you load ZZZ. DLL, the debugger looks for ZZZ. PDB. The extremely important part is how the debugger knows this is the exact matching PDB file for this binary. Thats done through a GUID thats embedded in both the PDB file and the binary. If the GUID does not match, you certainly wont debug the module at the source code level. The. NET compiler, and for native the linker, puts this GUID into the binary and PDB. Since the act of compiling creates this GUID, stop and think about this for a moment. If you have yesterdays build and did not save the PDB file will you ever be able to debug the binary againNo This is why it is so critical to save your PDB files for every build. Because I know youre thinking it, Ill go ahead and answer the question already forming in your mind no, theres no way to change the GUID. However, you can look at the GUID value in your binary.