Working with HTTP Cookies in SSIS or ODBC

Introduction

In this post you will learn how to Parse Cookies from Response and Pass Cookies to any Web Requests. SSIS PowerPack v1.8.2 Introduced new feature to send and receive cookies from HTTP Web Request/Response.

We will use SSIS REST API Task to parse cookie values and then send cookies along with HTTP Web Request. Cookies are very helpful if you want to maintain session state between multiple web requests.

Understanding Cookies

Lets look at an example how Cookie works. Cookie is two way communication. Which means you can Pass values to Server or Server can pass values to you.

Getting Cookies from Server (Web Response)

Many web api or sites sends you cookies back. If you are using Browser then it automatically saves for later use. Most common example is login pages. When you authenticate yourself server web page sends cookies back via Set-Cookie header. Server may send one or more than one Set-Cookie headers.  When you use SSIS REST API Task you can Map such value on Response Tab. However One important difference is REST API Task will only sends you one Set-Cookie Header with all cookies combined in Key1=Value1; Key2=Value2 … so on. You can use Cookie Tab to Parse each Cookie and save to separate variable (Explained in next section)

Web Request:

Web Response:

Passing Cookies to Server (Web Request)

User can pass one or multiple values in the form of Cookies to server. To pass Cookie(s) to Server you have to send Cookie header as below

Web Request:

Reading Cookies from Web Response using SSIS

SSIS REST API Task supports reading Cookie three different ways.

Method-1 (for SSIS / ODBC) : Dynamic Token with Retain Cookie Setting (Using Cookie from Login)

In the new version of HTTP Connection UI you can find Retain Cookie settings like below.

If you not receiving Token from the response and only want to use Cookies then set the following way (Enter Fake Regular expression (i.e. word like “SOME-FAKE” is fine )

Method-2 (for SSIS) : Reading / Parsing Multiple Cookies (Save to SSIS variables)

If you like to save cookie value(s) into separate SSIS variable(s) then you can use SSIS REST API Task >> Cookie Tab to configure Mappings. Perform following steps.

1. Configure REST API Task with necessary information (e.g. URL, Headers, Body)
2. Goto Cookie Tab. In the mapping Grid enter each Cookie Name and map to SSIS Variable like below. When you run SSIS Package you will see values stored into variable

Method-3 (for SSIS) : Read as raw cookie string

Some times you may need to see all cookies in a key/value pairs and save to variable. if that’s the case then Use Response setting tab and map Set-Cookie Response Header to some SSIS Variable

 

Method-4 (for SSIS) : Read Cookies  as CookieContainer

Now lets look at how to use CookieContainer Variable to Save Session across multiple API calls. All you have to do is Create Object datatype variable and select same variable across multiple tasks.

In below example first task is “Set Cookies” .. its similar to Login call in traditional API call where you get cookies bac from server. Call web url “http://httpbin.org/cookies/set?k2=val2&k1=myval1” this generates some cookies and place into variable called “varCookies” (Must be Object datatype)

In the next step we call url “http://httpbin.org/cookies” and we pass previously captured cookies from “varCookies” (Set on Cookies tab). And 3rd step displays response from URL (This demo URL sends back all supplied cookies in JSON format).

Pass Cookies to Web API (for SSIS / ODBC)

Now lets look at how to Pass Cookie(s) along with your Web Request

To pass cookie you have to use Cookie HTTP Header. You can pass multiple cookies in a single header as below

You can use SSIS variable to pass dynamic value as below screenshot. (e.g. MyCookieName={{User::MyVariable}} )