Migrating my Postman collections from one MS-Windows system to another

Right now I’m in the process of migrating developer stuff from my old MS-Windows pc to a new one. On the old machine I’ve used the Postman tool often to test and deploy new web-services. After having installed the tool on the new pc I wanted to copy my collections from the old pc. This article describes how to accomplish this.

• Start Postman on the old system
• My collections
  
• In the Menu Bar click on the Settings icon
  
• In the Settings dialog window, click on the Data tab
  
• Click on the Download button to download your collections in a dump file. The ‘Save As’ dialog window opens. Select a name and location on a file share that can be reached by the new PC.
  
• Save the file.
• Close the Postman tool on the old PC.
• Start the Postman tool on the new PC.
• In the Menu Bar click on the Settings icon.
• In the Settings dialog window, click on the Data tab.
• This time click on the ‘Select file’ button in the ‘Import Data’ section.
• In the ‘Open file’ dialog select the postman dump file you created in the file share.
• Click Open and that’s it. My collections are now available on the new PC.

SSIS 2012 package with Odata source–execution failed (part 3)

In post SSIS 2012 package with Odata source-failure (part 2) I was able to deploy my package with Odata source. But the package failed to run.

Change from Project deployment to Package Deployment

I have converted my project with package connections from project deployment to Package deployment. Then I deployed the package on the server in MSDB catalog from SSIS. I.e. I connect with SSMS to Integration Services as an administrator and under the MSDB folder I added a new folder for my package. I then imported the dtsx into the mdsb package store. The Import worked. Still from within SSMS and connected to the Integration Services, i right-clicked on the package in the MSDB store and selected 'Run package'. In the Execute Package dialog window, I clicked on 'Execute' and the package actually ran. The Odata connector was opened, it read the data and imported into my database.

This experience made me realize what the real culprit  was: The OData connector is a 32-bit connector in SQL Server 2012. 

Odata source is 32-bit

To prove this: with SSMS I connected to the SQL database server and in the Integration services catalog, i right-clicked on the package that was deployed in Project deployment mode, then selected 'Execute...'. In the execute dialog windo in the 'Advanced' tab I checked 32-bit runtime. And the package ran.

Final conclusion

1. The Project deployment will work, but not with the Odata source as project connection. The Odata connection must be defined in each package as a package connection.
2. Run the deployed packages in 32-bit mode on the server

SSIS 2012 package with Odata source–failed to deploy project

On a test server with VS 2012 and SQL 2012 installed, I have created an SSIS project in project deployment mode. In the packages of the project I use the Odata source for SSIS. The packages run fine in VS 2012. But when I deploy the package to the Integration Services catalog on the same server as where I am running VS 2012 I get a deployment error:

‘Failed to deploy project. For more information, query the operation_messages view for the operation identifier '25084'.  (Microsoft SQL Server, Error: 27203)

To find out the cause of the problem, you can connect with SSMS to the SSISDB database where the SSIS projects are stored. Then select the record with operation_id from the operation_messages as suggested in the above-mentioned errormessage.

use SSISDB
go

select * from catalog.operation_messages where operation_id = 25084

The message column in the result contains the cause of the error:

‘Failed to deploy the project. Fix the problems and try again later.:Unable to create the type with the name 'ODATA'.’

In my case the deployment failed of SSIS packages that use the Odata source on the same server as where I tested successfully with VS2012.

Now I have to find out what is wrong, see SSIS 2012 package with Odata source-failure (part 2)

Test LightSwitch OData services with Fiddler v 2.4.x

Make a visual Studio 2013 Lightswitch app based on the Northwind SQL database.

Application name: NorthwindLS

Data Source: NorthwindData

The Lightswitch app is published to an IIS server ‘myhost’

Normally the media format of the OData will be ‘atom’.

To show result in JSON format with Fiddler

Start Fiddler v 2.4.x

In the composer tab next to the GET command, enter the URL of the Odata service published by the Lightswitch app: http://myhost/NorthwindLS/NorthwindData.svc

In the textbox under the GET command, there are 2 lines of text that are sent to the server

User-Agent: Fiddler
Host: myhost

Add a 3rd line: Accept: application/json;odata=verbose

click the Execute button to execute the Get command. The response will be in JSON format.

Odata service with EF 6 and WCF services 5.6 fails with Request Error

Problem

I tried to follow a few more examples about building OData services with Visual Studio 2013, Entity Framework 6 and WCF Services 5.6, but when I tried to browse the Wcf (OData) service from VS 2013, it starts IE wiith an error message:

“Request Error. The server encountered an error processing the request. See server logs for more details.”

I tried various combinations, but to no avail (I even used another development system to try in case my development environment was botched up).

Solution

Eventually I found an anwser in stackoverflow.com foru, by Kourosh.

“it seems the entity framework 6 and wcf data services 5.6 , need some provider to work together. you can download the provider simply by using nuget package console manager :

Install-Package Microsoft.OData.EntityFrameworkProvider -Pre

it’s version is alpha 2, so in future, search for final release. it worked for me however.

final thing is instead of DataService, you need to use EntityFrameworkDataService. T is the name of your entities.”

Thank you Kourosh. Now my OData service with EF 6 and WCF data services 5.6 works!

Procedure

Step 1: install the package

Step 2: edit the WCF webservice c sharp code and replace DataService with EntityFrameworkDataService.

using System.Data.Services.Providers;

namespace DataAccessMydata

{

    public class ODataMydata : EntityFrameworkDataService<MydataContext>

Adding query options to the SSIS OData Source

 

In my previous article about SSIS OData source I created an SSIS 2014 package with an OData source connected to the Odata.org sample webservice.

OData query options

The OData Source has query options that can be used to alter the collection that is being used. I have made some tests on these options.

Option	Description
$expand	Expands related entities inline
$filter	Filters the results, based on a Boolean condition
$inlinecount	Tells the server to include the total count of matching entities in the response
$orderby	Sorts the results
$select	Selects which properties to include in the response
$skip	Skips the first n results
$top	Returns only the first n the results

See also Odata uri conventions paragraph 4

Set query options in OData Source editor

In the Data Flow select and edit the OData Source. The ‘Query options:’ textbox can be used to set options

$orderby

e.g. to order by the Product name: $orderby=Name. To Verify the result, click on the ‘Preview…’ button.

$filter

e.g. select all products with Rating 3: $filter= Rating eq 3

e.g. select products released in the year 2006: $filter= year(ReleaseDate) eq 2006

e.g. select discontinued products: $filter= DiscontinuedDate ne null

$top

e.g. select top 5 products ordered by Name descending: $top=5&$orderby=Name desc

$select

e.g. select only 2 columns on the products table: $select=Price, Name

$inlinecount, $format

Not usefull for SSIS purposes.

$expand

I will investigate this further once I have better samples than the odata.org samples..

SSIS OData Source available from Microsoft

A few days ago Microsoft has released an OData Source for SSIS 2012 / 2014. Downloads can be found in the links below:

Microsoft OData Source for Microsoft SQL Server 2012

Microsoft SQL Server 2014 Feature Pack

Documentation is available in the Books Online OData Connection Manager

Currently I am using SQL 2008 and 2012. There is no OData Source for SQL 2008. I’ve downloaded the OData Source for SQL 2012. Installed the msi file on my SQL test server and on my SQL Development PC with VS 2012.

Add an Odata Source to SSIS package in VS 2012

In a solution with the Project deployment model. Open the the Solution explorer, right click on Connection Manager, select New Connection Manager.

Select ODATA, Click on Add…

The OData Connection Manager Appears

For Service document location I have used the sample sources from odata.org.

http://services.odata.org/V3/OData/OData.svc/

Use Windows authentication to make the connection.

With the Odata Version 4 sample, the connection manager generated an error. Version 3 worked.

Click OK

The connection managers Tab shows the OData Source

Use the OData Source in a Data Flow task

IN the SSIS package with the OData Source, Create a new Data Flow task.

In the Data Flow Task add an OData Source. In the SSIS Toolbox the OData Source can be found under the Common tools, not under the Other Sources.

I cannot help but notice the return of the coloured icons (in contrast to the VS 2012 B&W scheme)

Drag the OData Source onto the Data Flow Task surface.

Edit the OData Source.

OData connection manager: OData Source

Use collection or resource path: Collection

Collection: Products (in the dropdown you will see all available collections (Products, Categories, Suppliers, etc…)

Click on preview to see the Products data (up to 20 first)

In the OData Source Editor look at the columns

Text columns are in Unicode text stream format, Dates are in database timestamp format.

For testing purposes I created a SQL table to accept the products:

CREATE TABLE [dbo].[ImportProduct](

    [ProductId] [int] NOT NULL,

    [ProductName] [nvarchar](255) NULL,

    [ProductDescription] [nvarchar](255) NULL,

    [ReleaseDate] [datetime] NULL,

    [DiscontinuedDate] [datetime] NULL,

    [Rating] [int] NULL,

    [Price] [decimal](12, 4) NULL,

 CONSTRAINT [PK_ImportProduct] PRIMARY KEY CLUSTERED 

(

    [ProductId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

In the SSIS Dataflow task I can now convert the data from the OData source to the format of the destination table in SQL server.

Next, an ADO.NET Destination is added.

The result after running the package: 11 rows are inserted into the SQL table.

Conclusion

The SSIS OData Source is a nice addition to SQL SSIS 2012 an d2014. It works with OData version 3.0. Now all we need is an OData Destination  component.

In my next post I have tried adding query options to SSIS OData source

Creating an OData Endpoint in ASP.NET Web API 2 tutorial

Recently I wanted to migrate a Web service that returns an array of database records to the most recent WCF service and return a List<T>.

Lightswitch can be used to generate Odata services almost without any coding.

But recently I have come to this set of articles, that explain step by step how to create a full Odata service with CRUD functionality in VS 2013: Creating an OData Endpoint in ASP.NET Web API 2 By Mike Wasson (feb 2014).