ssis step by step
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.0, and runs a unique package which stores the design of an ETL (Extraction -> Transformation -> Load) process.
Because SSIS in SQL 2005 was difficult to connect to Web Services by default, I did not spend much time into my research for my previous integration projects. 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.0 using 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, here’s the list of requirements:
- SQL Server 2005 Standard/Enterprise Edition with SQL Integration Service Installed
- Microsoft Dynamics CRM 3.0/4.0
- Visual Studio 2005 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.0 via 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 that’s already existed in environment.
Source Table: Contacts
First Name
|
Last Name
|
Phone
|
Email Address
|
John
|
Smith
|
312-888-8888
| |
Darren
|
Liu
|
312-999-9999
| |
Adam
|
Johnson
|
312-555-5555
|
1: CREATE TABLE [dbo].[Contacts](
2: [ContactId] [int] IDENTITY(1,1) NOT NULL,
3: [FirstName] [varchar](50) NULL,
4: [LastName] [varchar](50) NULL,
5: [Phone] [varchar](50) NULL,
6: [Email] [varchar](50) NULL,
7: CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED
8: (
9: [ContactId] ASC
10: )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
11: ) ON [PRIMARY]
12: GO
13:
14: INSERT INTO Contacts (FirstName, LastName, Phone, Email)
15: VALUES ('John','Smith','312-888-8888','jsmith@crowe.com')
16:
17: INSERT INTO Contacts (FirstName, LastName, Phone, Email)
18: VALUES ('Darren','Liu','312-999-9999','dliu@crowe.com')
19:
20: INSERT INTO Contacts (FirstName, LastName, Phone, Email)
21: VALUES ('Adam','Johnson','312-555-5555','ajohnson@crowe.com')
22: GO
Create CRM Proxy Class
The SSIS framework provides a Web Service Task which executes a Web Service method, however it’s 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.Proxy”
Sign the Project
- Right click on the project and select “Properties”.
- Click on “Signing” tab and check the “Sign the assembly” checkbox.
- Select “<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.
- Click OK.
Add CRM Web Services
Add CRM Web Service and CRM Discovery Service to the CRM.Proxy project. Visual Studio will automatically run WSDL.exe in the background to create a proxy class for these two 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 “CrmSdk” as the Web reference name. My CRM Web Service URL in this example is http://localhost:5555/mscrmservices/2007/CrmService.asmx
- Repeat the above step for CRM Discovery Web Service and give “CrmSdk.Discovery” as the Web reference name. My CRM Discovery Web Service URL in this example is http://localhost:5555/MSCRMServices/2007/AD/CrmDiscoveryService.asmx
- Compile the Crm.Proxy Project in Release Mode.
Deploy CRM Proxy Class to SSIS
In 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:\Windows\Microsoft.Net\Framework\v2.0.50727.
To GAC the Crm.Proxy.dll, you can simply drag and drop the dll file to C:\Windows\assembly 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.dtsx.
- 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 FirstName, LastName, 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 SSIS
Since 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.CrmSdk
Imports System.Xml
Imports System.Web.Services
Lastly, copy and paste the following code to the ScriptMain section:
1: Public Class ScriptMain
2: Inherits UserComponent
3: Dim Service As CrmService
4:
5: Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
6:
7: ‘Create Contact object
8: Dim contact As New contact()
9:
10: contact.firstname = Row.FirstName
11: contact.lastname = Row.LastName
12: contact.telephone1 = Row.Phone
13: contact.emailaddress1 = Row.Email
14:
15: Service.Create(contact)
16: End Sub
17:
18: Public Overrides Sub PreExecute()
19: MyBase.PreExecute()
20:
21: ‘Create CRM Service
22: Service = New CrmService()
23: Service.Credentials = System.Net.CredentialCache.DefaultCredentials
24: Service.Url = "http://localhost:5555/MSCrmServices/2007/CrmService.asmx"
25: Dim token As New CrmAuthenticationToken()
26:
27: ‘In this example, my organization name is MicrosoftCRM
28: token.OrganizationName = "MicrosoftCRM"
29: Service.CrmAuthenticationTokenValue = token
30: Service.PreAuthenticate = True
31: End Sub
32:
33: End Class
Execute the SSIS Package
After done coding the SSIS package, right click on the Contact.dtsx package and then select Execute Package. After the package executed successfully, we should see the records in MSCRM.
Deploy SSIS Package
After successfully test the package, deploying the package is pretty easy. Just follow the steps below.
- Right click on the CRM Integration project and the select Properties.
- Click on the Deployment Utility tab and set Create Deployment Utility to True.
- Recompile the CRM Integration project. You should now see CRM Integration.SSISDeploymentManifest in the bin\Deployment folder.
- Double click on CRM Integration.SSISDeploymentManifest and follow the wizard to deploy the SSIS package.
After the package is deployed to your SQL server, setup a SQL agent job to execute the package according to your integration time interval.
Summary
That’s it! Hopefully you have gotten the idea of how to leverage the power of SSIS to send data to CRM. In the example here, I only demonstrated how to create records in CRM. In the actual integration implementation, you will also need to consider how to update/delete/link records, and also error handling. I hope this will help you in your next CRM integration project.
