How to unblock a file when there is no unblock option

Funny things happen in the restricted environments. Normally, if you download a zip file from the internet, you can use “unblock” option to instruct the system that this particular zip file is safe. Well, not on that particular workstation which I had to work on today. There was just no “”unblock” option at all.

So, in case you are wondering what to do in such situation, and, also, just so there was a recipe for my own reference, there is a solution provided here (actually, even two solutions):

http://superuser.com/questions/38476/this-file-came-from-another-computer-how-can-i-unblock-all-the-files-in-a/38483#38483

 

The one solution that I did not try but that seems to be the better one is using powershell. You just need to run the following command:

dir c:\mydir -Recurse | Unblock-File

 

The other solution which I did try and which worked for me involves downloading one of those magical sysinternal utilities:

https://technet.microsoft.com/en-us/sysinternals/bb897440.aspx?f=255&MSPPError=-2147217396

Once you have it downloaded, just run the following command:

streams -s -d directory

The caveat here is that you might do some damage with this command if, for example, you ran it against your C drive.. so, make sure to use it only on the directories which don’t have anything but your files.

Dynamics CRM: client-side validations

The great part of working in a team is that you get to see some nice examples of how other people solve the same old (or, sometimes, new) problems. Take client-side validations in Dynamics CRM.. It’s always the same basic idea: create a javascript function that will be assigned as an event handler for the OnChange/OnSave events. However, most of the examples you will find online are lacking the “universality” – more likely than not, you’ll have to pass attribute name to the function.

However, there are two client-side SDK features which will allow you to create reusable validation logic.

Namely, those are the ability to pass execution context and the ability to set control notifications.

Once you have execution context, you can use getEventSource call to find out Xrm.Page object model that is the source of the event. Which means you don’t need to pass attribute names to your validation scripts, you just need to make sure “execution context” is passed as a parameter when configuring your event handlers.

As for the setNotification, that’s just a nice simple notification mechanism which you can use to stop CRM from saving data when/if your validation fails.

Here is an adopted example of how one of my colleagues implemented javascript validation to ensure that a text CRM field represents a valid year – you just need to create a web resource for this code, attach ValidateYear function as a handler for the field’s OnChange event, and ensure that “pass execution context” option is selected:

 

function ValidateYear(executionContext) {

  var attribute = executionContext.getEventSource();

  var fieldName = attribute.getName();

  var control = Xrm.Page.getControl(fieldName);

  var yearStr = attribute.getValue();

  if (/^\+?(0|[1-9]\d*)$/.test(yearStr)) {

    if(Number(yearStr) > 1900 && Number(yearStr) < 2100))

   {

      control.clearNotification();

      return;

   }

  }

  var errorMessage = “You must enter a valid year between 1900 and 2100”;

  control.setNotification(errorMessage);

}

HELLO WORLD! Moving the data..

 

Now that we have the project saved, let’s see how to make our data move from one MS SQL table to another.

First of all, remember that “For Distribution” option? Make sure it’s set to false. You don’t want to lose your connection settings while working on this project.

clip_image001

Now, on the left side, expand the “Database” tab in the component inventory area:

clip_image002

And, then, double click on the MS SQL Connection component in the component inventory. You should see a MS SQL Connection component added to the project designer area:

clip_image004

Let’s define connection properties by clicking on the “advanced” button(which you can see on the screenshot above):

clip_image006

You may have to use different SQL server settings – it depends on how it’s all set up in your environment. Still, once you have provided the server name (and, optionally, user name/password), you can click that refresh button and, assuming all the connection details are correct, you will need to choose from the list of databases:

clip_image008

So, select the database.. Click “OK”. You will notice that component’s connection string has been updated:

clip_image010

That’s it, we have our SQL connection configured.

Now we need to add two data sources (one for the source table, and one for the destination table).

You can do it in just the same way you did it with the dataconnection.. First, re-arrange MS SQL Connection on the designer surface so it’s in the left top corner (use mouse for that – just drag the component across designer surface):

clip_image012

Then, double click on the MS SQL Data Source and move new component a little up to the left:

clip_image014

In the “Name” text box, type in “Source Table”:

clip_image016

Add another SQL Data Source component and call it “Destination Table” this time:

clip_image018

Now, let’s connect all those components on the surface. There are connection points on the left, top, right, and bottom sides of each component. If you click on one of the connection points, hold left mouse button down and start moving the mouse, you’ll see a connection pointer.. Keep moving the mouse until you reach your target components, then let it go. Let’s set up two connections: from MS SQL Connection to Source Tabe and from Source Table to Destination Table:

clip_image019

The reason we want to have a connection between MS SQL Connection and the Source Table is that we will control project execution sequence that way. Even though no actual data will be passed from MS SQL Connection component to the Source Table.

Now let’s configure the Source Table component.

Do this:

– Select “Source Table” component. You can select any component on the designer surface and have a look at its properties by clicking on that component.

– Leave “Action” as “Select”

– Choose MS SQL Connection from the “Connection” dropdown

– Once you’ve selected the connection, choose “Source Table” for the “Table” property

clip_image021

For the “Destination Table” component, use “Insert” action and “Destination Table”:

clip_image023

We are almost there – just need to configure field mappings from the source to the destination.

While “Destination Component” is still selected, click “Advanced” button in the “properties” area:

clip_image025

This is where you can configure mappings. You can see how Id and Name fields are available at the destination.. And you just need to choose the source fields which will be used as a source:

clip_image027

Click OK. The project is ready – you can start it and see how your source table data goes to the destination table.

Before you do, save the project just in case (File->Save)

Then, from the Project Menu, select “Start”:

clip_image028

It’ll take a moment, and you’ll see this kind of picture on your screen:

clip_image030

As you can see, exactly 2 records have been moved from the Source Table to the Destination Table as a result. You might want to verify it in the SQL Management Studio just in case..

There is also one magical record that was passed from the SQL Connection to the Source Table. We don’t need to worry about that one, though, it we wanted to capture it, we could. There is some information about the connection in that record, but it’s of no interest for now.

And that’s it – our first “Hello World” project is up and running. It might not be of much practical use, but it shows how EZETL works and how easy it is to create ETL packages in this tool.

Hello World!

Creating your first EZETL project

Let’s do something simple first, and, then, we will move on to a more complicated (but, hopefully, to a more rewarding) stuff. For this one, let’s create a project that will be moving data from one table to another in your MS SQL database. Also, for the sake of this exercise, let’s ignore the fact that you can do the same in the SQL Management studio using a relatively simple query.

First, here is what you’ll need:

  1. A Microsoft SQL Server instance – SQL Express would do just fine
  2. A database, which I will be calling “EZETLTest”.. but feel free to use any other database name
  3. A couple of tables so you could move data from one table to another. Let’s call them SourceTable and DestinationTable. You can use the SQL below to create those tables:

CREATE TABLE SourceTable

(

Id INT,

Name NVARCHAR(100)

)

CREATE TABLE DestinationTable

(

Id INT,

Name NVARCHAR(100)

)

  1. Before we go any further, let’s add some data to the SourceTable

INSERT INTO SourceTable (Id, Name) VALUES (1, ‘First’)

INSERT INTO SourceTable (Id, Name) VALUES (2, ‘Second’)

So far so good – we are now done with the preparations, so we can move on to creating an actual EZETL project.

I’m assuming you’ve already installed the software, you have added your license keys. If not, please refer to the EZETL Installation article to see how those steps are done before proceeding to the rest of this post.

Let’s open EZ ETL

clip_image002

Notice that, when you open it, you already have “New Project” open by default. You can give it a different name:

clip_image003

And you can give it a description:

clip_image005

In both cases, just move mouse to one of the properties on the right side and click in the corresponding text box. You will either be able to work directly in that text box, or, as it happened above for the “description”, you’ll be presented with a popup window where you can enter desired details.

Now let’s save the project so we don’t lose our work.

clip_image007

Notice that you can also open an existing project.. you can create a new one.. Or you can save your project into a different file (“save as” option).

Now at this point you might want to ask how/where the data is saved. And the answer is: EZETL projects are saved as “xml” files. Some parameters might be encoded, others might not be encoded. As a rule of thumb, you can assume that sensitive data may show up in those files if you take no precautions.

There are a couple of things you can do:

  1. Store your projects in a secure location – use file access security to make sure nobody else can get to those files
  2. Do not put any sensitive information into those files. For example, when creating various connections to CRM, Database, etc, use integrated authentication – that way you won’t have to leave user names/passwords in the project file
  3. Set “For Distribution” to “True” in the project properties:

clip_image008

This one can be a little tricky, though. It will be up to the specific data component to define properties which cannot be saved in the project file when “for distribution” is set to true. For example, this will prevent MS SQL connection parameters or Dynamics CRM connection parameters from being saved. However, this feature is, likely, more useful for project distribution rather than for protecting the data in general since you would use it to create projects you want to send to somebody else. Just like we do it with sample projects here, for example.

But we’ve really got sidetracked. Remember we just changed the name & description, saved the project.. And we have not done any work with the data yet – that’s what we are going to do next.

EZETL Installation–let’s get it started..

EZETL Installation Overview

  1. There is no dedicated setup program for EZETL – instead, you can use x-copy approach to the installation by extracting the contents of the provided application zip file to a folder on your disk
  2. You can install the application at any time, but you have to make sure your system meets some basic requirements. Namely, here is what will be required:

     – Full version of .NET 4.5 or later

  1. Application installation zip file includes a number of components:

     – EZ ETL Project Designer

     – EZ ETL Scheduler

     – EZ ETL Integration Packages which are developed by TreeCat Software (you can still develop your own packages and/or download and install those developed by others)

  1. Once you have extracted the contents of the zip file, and assuming you have .NET installed and you also have sufficient privileges on the machine, you can begin working with the software – just start EZETL.exe application, accept all the licensing terms, and provide your license key information to unlock application functionality

 

EZETL Installation Steps and Screenshots

  1. Unpack the contents of the installation zip file to your disk

clip_image001

  1. Start EZETL.exe to begin working with the application. You will be asked to accept application license terms – you may be asked more than once, depending on whether there are specific license terms for any of the included integration packages

clip_image003

clip_image005

NOTE: You have to accept EZETL Application Software license agreement to be able to proceed. You do not have to accept individual packages license agreements, however, if you don’t do that, those packages will not be available to you.

  1. Once the license agreements have been accepted, the application will start, and it will ask you for the license key:

clip_image007

  1. Assuming you’ve already received your license key, you can add it to the application by following this simple procedure:

     – On the screenshot above, close the message box (click “OK”)

     – Open Package & License Manager from the application menu

clip_image008

     – Using Add License button, add a license key

clip_image010

     – Once the license key has been added, License Expiration Dates will be updated accordingly

clip_image012

     – Finally, click ok in the Package Manager window. From here, you are ready to start building your first integration project

clip_image014

We will have a look at creating our “Hello World” integration project in the next post..