SOLVED ERROR - Cannot find assembly "Microsoft.SqlServer.ManagedDTS" when calling an SSIS agent job from C# code

In this article we describe how to find the assembly "Microsoft.SqlServer.ManagedDTS"  when calling an SSIS agent job from C# code.

When calling an Sql Server Integration Services agent job SSIS programmatically from C# .Net code, in order to use the method  Execute , there is a need for using in the application the reference to the assembly  Microsoft.SqlServer.ManagedDTS .

The problem resides in finding that DLL . It should be installed in the pc, together with the rest of SQL SERVER DLLs.

How to find the assembly "Microsoft.SqlServer.ManagedDTS"  when calling an SSIS agent job from C# code

The first try will be to add the assembly via the assemblies list box in visual studio , only to find that it was not available there:

Then, we could go to the  SQL SERVER assemblies  folder : C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
Again, it will not be possible to get the assembly that way:

SOLUTION: 

Press WIN KEY + R to open the Run window , and run the GAC command : 

    C:\Windows\assembly\gac_msil


The GAC will open, and then make a search for the ManagedDTS assembly :

Copy it to the BIN folder of your VISUAL STUDIO project, then open the reference manager and BROWSE for it in the BIN folder :

Add the "Using" reference to your Project .

That's all...
In this article we've seen Step by step how to find the assembly "Microsoft.SqlServer.ManagedDTS" when calling an SSIS agent job from C# code.
Enjoy Microsoft Dynamics 365 CRM!

by Carmel Schvartzman

כתב: כרמל שוורצמן

How to watch SSIS Variables values at debugging in Sql Server Integration Services

In this article we see Step by step How to watch SSIS Variables values at debugging in Sql Server Integration Services .
We'll review here in only 5 minutes how to check SSIS variables at runtime, telling apart those at Foreach Loops from those at Data Flows  . We'll be using the following SSIS application:

How to watch SSIS Variables values at debugging in Sql Server Integration Services

In the picture below you can see the guiding principles of watching variables at runtime  in a  "Data Flow Task" :

To start, select the SSIS item in which you want to watch the variables values.
Since variables have  scopes,   you should make sure that the current scope comprehend the chosen item.
We'll start by setting a breakpoint in a Data Flow Task .
Now that you decided which flow item to watch,  click the "Edit Breakpoints" menu  :

Now choose the Break condition where to stop the debug . Mostly it can be the OnPreExecute event, or any one of the several Events that you can see on the following picture:

However, in case of a Foreach Loop Container, it stands to reason that you will want to watch the variables at the start of every loop iteration . So select from the checklist the "Break at the beginning..."  option:

It can also be set in which cases to stop the runtime: it can be set to stop when the breakpoint has been reached certain times:

Once you finished setting the breakpoints, start the debug and, while the breakpoint has been reached, open the "Locals" window. There you will find all the current variables in scope:

That's all.  

Happy programming.....

      by Carmel Schvartzman

כתב: כרמל שוורצמן

How to name SQL parameters in an SSIS Execute SQL Task

In this article we describe Step by step How to name SQL parameters in an SSIS Execute SQL Task , to avoid the common Sql Server Integration Services error : "Parameter name is unrecognized."  
We follow here the conventions introduced by  MSDN in the SQL Server documentation for SSIS . For explaining naming SQL parameters in an Execute SQL Task ,  we will make use of the following SSIS application:

How to name SQL parameters in an SSIS Execute SQL Task

We will want to give to the SQL parameters a proper name in order to avoid  the "Execute SQL Task" error, that can be seen in the picture above   : "Parameter name is unrecognized." :

The  MSDN Documentation explains that naming conventions for using SQL  parameters in OLE DB  should be as follows:

Suppose that we have this SQL query which is using parameters. Then we must use the "?" marker this way :

All parameters must be mapped to the query markers, and indexed base "0" . So, as we have 4 "?" markers, we must name 4 parameters indexed from "0" to "3" ,  using integer numbers conforming to the index positions:

      WHEN ?  - ? <  0          

      WHEN ?  - ? > 0   

        

     

    

That's all...Enjoy Dynamics CRM

by Carmel Schvartzman

כתב: כרמל שוורצמן

How to install Business Intelligence Development Studio (BIDS) for Visual Studio 2012 Sql Server 2008

1. In this walkthrough we will learn How to install Business Intelligence Development Studio (BIDS) on Visual Studio 2012 and Sql Server 2008 
2. The Business Intelligence Development Studio (BIDS) is an add-on to Visual Studio 2012 and Sql Server 2008. The BIDS includes the SSIS - Sql Server Integration Services templates for developing SSIS projects on Visual Studio 2012. Here we'll see step by step how to install it :
   
   

How to install Business Intelligence Development Studio (BIDS) on Visual Studio 2012 Sql Server 2008

1. Browse to the Microsoft Download Center to get the Visual Studio 2012 templates for the BIDS, which also includes the SSIS - Sql Server Integration Services :
   
   
2. Download it to your machine and run it as administrator:
   
   
3. Click the "Install" button :
   
   
4. Make sure that the Business Intelligence for Visual Studio 2012 box is checked  :
   
   
5. Click "Next" :
   
   
6. Check that the installation has been successful :
   
   
   
    
7. Now let's see how it has the Visual Studio 2012 upgraded. Before the installation, Visual Studio 2012 templates for new projects looked like this :
   
   
8. Now, after the installation, the Visual Studio 2012 templates include the ones for Business Intelligence , SSIS between them:
   
   
9. Now you can create a new SSIS - Sql Server Integration Services project using the Business Intelligence Development Studio (BIDS) on Visual Studio 2012 and Sql Server 2008 :
   
   
   


10. In this walk-through we've learned how to install the Business Intelligence Development Studio (BIDS) on Visual Studio 2012 and Sql Server 2008 .
11. That's all...Enjoy Dynamics CRM
    
    by Carmel Schvartzman
    
    
    כתב: כרמל שוורצמן
    

    
    

How to consume the CRM 2011 Organization (SOAP) WCF Services programmatically

by Carmel Schvartzman

1. In this walkthrough we will learn  Step By Step  How to call the CRM 2011 Organization (SOAP) Services  in Dynamics CRM 2011. We'll call the CRM 2011 WCF Web Service and perform CRUD operations using Indexing on the generic Entity class.  We'll consume the CRM 2011 Organization (SOAP) services from a class which can also be used by a console application, or a Windows Service, or a Windows Form application.
   We'll not be using the Microsoft CRM SDK 2011; instead we'll reach directly the CRM 2011 WCF SOAP endpoint to perform the CRUD operations. That means, we will not need an early binding proxy with the classes and methods available at the CRM organization. Instead, we will use Late Binding in our calls to the CRM WCF service.
2. By enabling Indexing we mean that we could access an Entity property getting/setting its value, by using an " entity["field_name"]  "  sintax, while reaching Crm2011 from a WIN app via its Organization Web Service. Therefore, a code like the following....:
   
   
   ... will allows us to create a new Contact, for example:
   
3. In order to create a client for the CRM2011 Organization Service, we'll create a .dll which will send requests to the CRM2011 Organization SOAP Web Service Endpoint. Then, we'll create a WIN project to use that assembly and fetch the CRM data, and also create new entities.
4. So let's create a new Class Project in Visual Studio 2010 or 2008, selecting the target framework to be version 3.5:
   
5. Next, add the CRM 2011 Web Service reference to the project:
   
6. Remember that we'll be using the Organization CRM SOAP Web Service, so type the address:
   
7. After adding the Web Service reference, rename the Class :
   
8. Type an static method returning an IOrganizationService object. This method will receive as parameters the host, the CRM organization and user logon data:
   
9. Now add the following security code for reaching the CRM Web Service :
   
   
   
   
   
   SymmetricSecurityBindingElement security = new SymmetricSecurityBindingElement();
   security.ProtectionTokenParameters = new SspiSecurityTokenParameters();
   HttpTransportBindingElement httpTransport = new HttpTransportBindingElement();
   httpTransport.MaxReceivedMessageSize = Int32.MaxValue ;
   CustomBinding binding = new CustomBinding();
   binding.Elements.Add(security);
   TextMessageEncodingBindingElement encoding =
   new TextMessageEncodingBindingElement(MessageVersion.Soap12WSAddressing10, Encoding.UTF8);
   binding.Elements.Add(encoding);
   binding.Elements.Add(httpTransport);
    
10. Now add the WCF Endpoint code:
    
    
    
    EndpointAddress endpoint =
    new EndpointAddress(new Uri(string.Format("{0}/{1}/XRMServices/2011/Organization.svc", host, organization)),
    EndpointIdentity.CreateUpnIdentity(string.Format("{0}@{1}", user, "")), new AddressHeader[] { });
    
    
    The "host" variable holds the name of your CRM server , and the "organization" variable is the name of your organization.
     
11. And finally add the code to create the Client:
    
    
    
    
    
    OrganizationServiceClient client = new OrganizationServiceClient(binding, endpoint);
    client.ClientCredentials.Windows.ClientCredential = new System.Net.NetworkCredential(user, password, "");
    
     
12. Check that we have all the "usings" we need:
    
13. Also, check whether all the references are listed:
    
14. Now, in order to perform the testings on our class, create a new console Project , which will call our CRM assembly:
    
15. Add the following references:
    
16. First, add the Runtime.Serialization assembly:
    
17. Second, add our class assembly:
    
18. Now, code a call to the IOrganizationService:
    
19. Add the corresponding using:
    
20. ... and type the relevant logon data, for security reasons:
    
21. Next, select some columns that you want to retrieve, and instantiate the entity object:
    
22. Check in the QuickWatch window that you get an account with the required attributes:
    
    
    
23. Now, try to use the  "account["field_name"]" sintax, that means to use Indexing on the Entity class:
    
24. It seems that,  if we want to use the "account["field_name"]" sintax, we'll be confronted with an error:
    
25. Same thing will happen if we try to create a new record, let's say a new Contact:
    
26. According to the error message, Indexing cannot be applied to an Entity.
27. To solve the problem, let's add Indexing support to the Entity class. We'll extend the Entity class by using two collections: the FormattedValueCollection and the RelatedEntityCollection:
    
28. Lookup also the RelatedEntityCollection documentation on MSDN:
    
29. Both collections are defined as OptionalFieldAttribute on the Serialization assembly, meaning that the formatters will not require such fields while serializing the Entity class:
    
30. Take a look at the FormattedValuesField in the "References" .cs file on your project:
    
    
    As you can see, the Entity class is partial, can be extended, and the FormattedValuesField is an optional collection.
    
31. Therefore, the first thing we'll do is instantiate those two fields in the partial Entity constructor:
    
32. Next, we'll add the Indexing feature to allow a "entity["field_name"] " sintax:
    
33. Then, code the get/set functions of the indexing using the AttributeCollection class:
    
34. Press "F12" on the AttributeCollection to see that object:
    
    
    It's a generic List<> of  KeyValuePair<string,object>, therefore let's code according to that.
35. Create a static class to hold the Extensions we need to interact with that List<>. Inside the static class, type the get - set extension methods for that List<>:
    
36. There are two simple cases that can happen while getting-setting a value from-to a collection. Let's code against them first. In the case of the "get" extension method, prepare to throw an exception if the key does not exists:
    
37. In the case of the "set" extension method, the value does not exists in the collection, so just add it to the List.
38. Next, the two important cases are when the value is in the collection , and we need to know the index it is in, in order to fetch it....:
    
    
    ...and when the value is in the collection, and we must override it with the new value. In both cases we'll use a method to get the index of the cell holding the value: GetIndex<K,V>(IList<> col,K key, out i).
39. Create the  GetIndex<K,V>(IList<> col,K key, out i) method:
    
40. Code the basic case in which the collection is null:
    
41. .. Next add the code for the Index search:
    
42. We're done. Compile and run the WIN Startup Project which uses the assembly:
    
    We can see that the Indexing is now working, and we can fetch the Account name, and also create a new Contact record.
43. Finally, let's check our CRM 2011 Workplace to see the new Contact added:
    
    
    
    
    That's all...Enjoy Dynamics CRM
    
    
    
    כתב: כרמל שוורצמן