Pablo Aizpiri

My experiences and thoughts on [mostly] technology.

My Experience with World Nomad/Trip Mate Travel Insurance

TLDR: I made a claim, I got paid; I want to write in more detail since it seems like these types of experience descriptions/reviews are lacking.

Highlights:

  • Make sure you understand what your policy covers. It seems a common phrasing is that the event should be “unexpected”. If you reasonably expected the event to happen, it probably isn’t covered by your policy unless your purchased a pricy cancellation-for-any-reason policy.
  • You are expected to make a reasonable attempt to get your money back. (you’ll need to attempt getting refunds from all vendors)
  • You’ll need to provide documentation to support your claim. The documents you may need are:
    • Receipts of the expense
    • A copy of cancelation policies
    • Flight itinerary/some proof of travel
    • Credit card or bank statement showing that you made the purchase and that the statement belongs to you
    • Medical Certificate from GP or an APS (Attending Physician Statement) signed by your physician if claim is medical in nature. Use the form insurer provides you.
    • Potentially, proof that you attempted to get a refund.
  • It took ~45 days from claim submission to receiving e-check.
  • Note you will receive a checks per covered traveler.
  • Still recommend refundable rates where possible. It’s just a lot easier/faster to deal with (and often only costs $10-20 for hotel rooms)

Why I’m Writing This Review

First and foremost, I don’t get paid for this or any sort of kick back or anything. I’m just hoping to “give back” to the online community and help other travelers.

My wife and I had planned on a 2-week trip to Italy in May, and I decided I should probably get travel insurance for such a large (and more expensive than usual) international trip. After much research, I went with World Nomads.

It was frustrating at the time seeing lots of reviews, but no/few reviews that detailed the process of actually making a claim and receiving funds . The ones I did read were almost always negative or had very little detail. This seemed to be the case for most insurers- probably most of the people writing reviews are frustrated enough to write. (Hell, I almost didn’t write a review either- after all, I got my money back, right? Why do any more work?)

A problem seems to be that travel insurance companies may work with travel bloggers to “review” their insurance product but it’s a fairly useless review because all the reviewer can do is talk about the insurer, the policy, the ease of use, and the website- they don’t actually make a claim. That’s fine but I mostly want to know I wont run into any shenanigans when I file my claim.

That leaves us with a bunch of bad reviews for those that do make claims- and when that’s the case for almost every insurer, it’s almost just as bad as when everyone writing a good review or no review- because it doesn’t add any meaningful signal. This review is my attempt to help with that.

What happened

My wife got pretty sick just a few days before out trip (high fever, dizziness, weakness, etc.)- and we knew it was almost certainly COVID-19. Then she tested positive. Next morning she called in sick for work- and her boss actually told her other employees had also caught COVID-19. So yeah. This was literally 2-3 days before our trip, and it was immensely disappointing. But! We had purchased the “Explorer Plan” through World Nomads (specifically N300E)- so I felt like even though it might take some work, I should be covered for this exact situation.

I had read horror stories for the claim processes- so I instantly went to work. I figured collecting proof quickly would be critical to my claim after having read through the policy-so the first thing I did is tell my wife to make sure we got a doctor’s note ASAP. It turned out later this wasn’t very helpful, because we needed a specific insurance-provided note signed by the doctor- but whatever, she sent this and we got a letter from her doctor as additional evidence.

I then went to the World Nomads site and began the claims process.

Intermission: A note about World Nomad’s Website

The World Nomads site could be great. It’s pretty close to being great. But it has a bunch of really annoying bugs: (at least as of 7/9/2022)

  • Sometimes you’re logged out or a page crashes and to fix it you need to log in and out again. (they even ask you to do that)
  • The “Need Help?” section at the bottom literally has broken links. Clicking “Contact us” or “Help Desk” takes me to an unreachable URL (https://service.worldnomads.com/)
  • The design is clunky. If I log in, I will want to go see my claims. Why else would I be logged in? It’s weird you have to go to the top-right corner, under MEMBER tab, hit the expand button under MEMBER DASHBOARD, click MY CLAIMS, and finally see claims there.
  • Right on cue for their clunky/buggy interface, is an admission of the state of things along the top in red: “If you can’t see an old claim on this page then click here to view it in our membership console.” Why can’t you show them all in one place?

I’m a software engineer by trade, so I might be being a bit harsh- but I really don’t think so- for a company whose their entire business model is online insurance they really should do that as best as possible. And that is their model- because as far as I can tell, they don’t actually insure you- Nationwide does. Not that that is a bad thing.

World Nomads seems to specialize in having designed two flavors of travel insurance policies that they feel make sense for most travelers- but the actual claims are administered by Nationwide Mutual Insurance Company.

In my mind what World Nomads offers is the unique policy they’ve designed and negotiated, a competitive price, and (theoretically) a smooth online way to navigate the claims process. It’s fine they’re not the administrators- but if their bread and butter (and the face of their business) is this online portal- they should really try to get that down.

All that said, where World Nomads really shines is in hand-holding first time customers through the actual claims process. And to the extent this can be done online- they do a decent job.

Back to what happened- the claims process.

I jumped online and began the claims process. They had a Wizard with multiple steps to walk me through the process- and that was actually pretty nice. I began filling it out section by section:

This took a while, and gathering the documentation was by far the most tedious process. Luckily, I keep digital receipts of everything- but it still took 4-6 hours overall. At the end, there was a nice checklist page they generated for me to help me check off each of the remaining documents I needed:

I was also expected to have attempted to get my money back- to ask for a refund. This kind of makes sense, but I wasn’t expecting it- took a little bit of time to write all the vendors, but I was surprised at how often businesses “bent” the rules and returned the money.

For example, Airlo- which I was using to get a e-SIM so I could use my phone in Italy without absurd charges, has a policy against refunds but they made an exception for me when I wrote to them. (Which on a side note definitely increased my confidence in using them in the future – so good customer support on their end)

The documentation that had to be compiled was:

  • Proof of travel: relatively easy- I simply provided my flight tickets and itinerary.
  • Receipts: self explanatory
  • Cancelation policies: Unexpected; but all I had to do was to find each cancelation policy on each vendor’s website.
  • Medical proof– originally I had asked my wife to request one in writing but I realized as I got towards the end of their wizard that there was a specific document of theirs that I needed to have her doctor sign- so we had to go back and get that. That was a pain but it was necessary.
  • Bank/Credit Card statement. This one was a PITA. Some online credit card interfaces will not show a long enough list of transactions to where you can see all the expenses and that the statement belongs to me. Also, most card websites have security features that prevent them from being saved such that you can still see the transactions; but after some “finagling” I got what I needed from my card’s website.

Armed with all my proof, I submitted it all. Then, we waited…

About ~10 days later I received an email from Trip Mate that they had received my claim- and then I was just waiting again.

More radio silence. After 3 additional weeks, I respond to their original email asking for a status update- they respond a week later asking for my claim number in order to help. (fair enough). I don’t ever hear back.

About 10 days after that I decide I’m going to call them. It took a while to get them on the phone, but when I finally did, they were actually pretty helpful. Turns out they had approved the claim, and at this point it had to be “batched” and the check “cut”.

Sure enough, a couple days later I got an email about an e-check that I should expect. It had been 46 days from claim submission to e-check delivery. The day after that I got an email from “deluxe payment exchange” which is the company that they apparently use to pay out claims.

One minor thing to note is that you get paid per traveler. So in my case, my wife was the one who was the subject of the claim but it caused a cancellation for both of us; so we each got a check for the cost of the trip split in half. I think the idea here is to prevent pooling costs when insuring the trip but then double dipping to get extra money if some of the travelers are still able travel.

Deluxe Payment Exchange

My experience using deluxe payment exchange was pretty crappy- they have what I’ve heard called “asshole design“. They specifically designed their product in a way that tries to force you to do what they’d prefer over a good product experience or what you actually need. Thankfully- it’s not too hard to get past it.

When you first get the email and “link to your check”, they want you to create an account (Why does everyone want me to create an account to their system? To cash a check? Really?).

You can avoid creating the account, but if you do they show you a big scary warning about getting to see the check only once and that if you don’t print it then and there you’ll lose it. To be fair, if you click it and your computer somehow instantly shuts off for some reason, it would probably be a huge PITA to figure out how to get them to send you a new check link – but I think that’s mostly due to their design choices. I suspect the real reason they try to steer you away from that is because you get directly to the check and can be done, which they don’t want.


When you do create an account, you are taken to some screen where you can see the check but it’s got a watermark on it so you can’t print it. You can download a “copy” (there’s a FILE COPY button), but it has a big fat “FILE COPY” watermark as well so you can’t print it either. Finally there’s a button to get your money, which when you click has a bunch of expensive options (except for the last).

The options cost a fee + some percentage… ACH transfer, credit card, etc. So if you’re getting charged a percentage of your claim to have it deposited in your bank, it adds up. For me some of these options were $80 just to move the money to my bank. The last option is that you can print a the check, but they give you a warning about sending and it taking 7-10 business days.

Ugh. It’s an e-check. I should be able to just print it, scan with my bank app, and be done. There’s no need to snail mail it. The only reason for it is to coax you into forking out more money.


I reluctantly clicked that last option just accepting I’m going to have to wait another week. When I did, it allowed me to download the PDF immediately with the check, no watermarks, and instructions to send/print…

Why isn’t printing my check the very first option?!

Anywho, I printed my check, scanned it that night, and within 2-3 business days it showed up on my Ally account.

What expenses were covered?

Everything trip related: Flights, lodging (non refundable cancelations, AirBnB partial refunds, etc), bus/train transportation, and the few tours that were non refundable (surprisingly most were!)

Conclusion

I would use World Nomads again and definitely get trip insurance at least for international/expensive trips. Lots of travel credit cards also offer travel insurance nowadays, but I was hesitant to use mine. I did actually try to go through their website and it was terrible. The submission page was actually broken, and they try really hard to be secondary insurance. Their coverage also wasn’t as comprehensive. That said, for trips within the US I’ll probably use that.

Paying ~$280 for peace of mind was definitely worth it for my trip which was one of the more pricy ones for me- as well as for the medical coverage, trip interruption, etc. It certainly paid back for itself, as in the end my reimbursement was over $3,500.

That said, I still recommend splurging the often $10-20 extra for refundable room rates on a hotel- refunds are just so much quicker and easier. If possibly and cheap try to get cancellable rates/refundable reservations for anything you can. But the insurance is nice for everything else (and some stuff like airline tickets is far too expensive to get refundable rates on).

Hopefully this helps someone out there. Feel free to add your experiences or if you have anything to correct about my understanding of World Nomads/Nationwide/insurance process.

Good luck, and safe travels!

Pablo Aizpiri

Note: I also posted this on reddit here. Due to that subreddit’s “no blogs” rule I didn’t link to my blog. The only difference between the two is here I have some more pictures and links to the forms.

Unreal Engine Course

I’ve always had a soft spot for game engines and game programming- since that’s partly how I got started writing software. But writing a game from scratch- especially a 3D one has traditionally been really challenging. (This is a really good video on the topic)

Nowadays it’s much easier to make amazing looking games and there are so many options to do so. I know that, but I didn’t realize just how much better of an experience it is. I recently decided to start learning Unreal Engine and I’m very happy/impressed with results so far.

Some screenshots and a video on some of what I’ve been able to build (using an existing assets pack of course) during the “Unreal Engine C++ Developer: Learn C++ and Make Video Games” course on Udemy:

 

 

Video (“Room Escape” project)

On a side note, if you already have a programming background, the first part of the course can be safely skipped (I did) but the rest is very helpful!

.NET Serial Communication and Microbotlabs’ Robotic Claw

Microbotlabs Robotic Claw

Microbotlabs Robotic Claw

I always enjoy playing with electronics and seeing what I can do with them, so I’m a big fan of Arduino and the maker movement. One area I had been wanting to explore with electronics was real world physical interactions, like moving robots or robotic claws. So I added a robotic kit to my wish list on Amazon and just so happened to get one for the holidays!

This is the Microbotlabs “MeArm DIY Robot Arm Kit With MeCon Pro Motion Control Software and Arduino Source Code” that you can get on Amazon MeArm DIY Robot Arm Kit With MeCon Pro Motion Control Software and Arduino Source Code. The kit quality is a little low (especially the servos) and the instructions and support seem lacking; but I was able to put it together and it was still a lot of fun and pretty cool. Once I built the claw, I connected it to my Arduino and tried running the application Microbotlabs include to control the claw from your computer (MeCon).

With everything working, I toyed around with the application for a bit. The idea of “recording” servo positions and playing them back so that the robot could record and playback motions seemed like a fun way to interact with the arm. Below is a screenshot of the application, where you can see the controls. There’s the ability to move the base and each of the 3 servos controlling various parts of the arm, and the on the right there is a list of “coordinates”. These are servo positions, and you can use the application to record a list of these “coordinates” and play them back so that the robotic claw can perform a specified movement (and presumably so you can generate code from it that you can maybe add in your project).

Microbotloabs MeCon application

Microbotlabs MeCon application

This was fun at first, but it very quickly became obvious it was going to be difficult to record and playback smooth motions without a lot of work. I would record some movements and then I would play the recording back, but the Robotic Claw would jerk and jump to the new servo positions. I was either going to need to record a lot of motion data or just accept jerky movements. Adding insult to injury, it was also difficult to edit a motion once recorded. Here’s a short video of playback of the motion recording with the standard software:

To be fair, I’m sure if I would have spent enough time adjusting and tweaking I could have gotten that to work somewhat, but I still had limitations. I decided it would be fun to build my own interface to address these problems using .NET and very generically called it “Crawl Controller”:

.NET ClawController Application

.NET “ClawController” Application

Basically, the major feature difference is that you can move the claw to the desired position and record “key frames” much like 3D animation for 3D models in video games. On playback, you can have the application interpolate in between each key frame and come up with the “frames” in between so that the motion is nice and smooth. I went ahead and put all my code (which wasn’t the prettiest, but hopefully clear enough) on GitHub so anyone can download and play with it. The application also makes it easier to delete and add new key frames wherever desired which really comes in handy when “designing” a new movement. The application also allows the user to send more precise servo positions rather than just a degree (180 degrees), but the application is still backwards compatible with the code that Microlabs provides out of the box. (See GitHub for Arduino code that allows “Crawl Controller” to use more precise servo positions)

Anyway, here is a video of the finished product with interpolation. It is much easier to put together complex motions and the smooth movement allows much more gentle motions making it easier to pick up and set down objects consistently and repeatedly. Here, I recorded movement for the claw that loops and you can see the claw can repeatedly put down and pick up the VGA/DVI adapter again after finishing the animation without knocking over the adapter- the animation also was easy to record.

Code on GitHub: https://github.com/JhottMaster/ClawController

Exchange Dynamic Distribution Lists

This is another PowerShell script one of my coworkers came up with; it creates dynamic distribution lists. It can be handy to create distribution lists in AD based on location, for example, instead of manually managing a distribution list.


New-DynamicDistributionGroup -Name "123 Building" -RecipientFilter {(RecipientTypeDetails -eq 'UserMailbox') -and (StreetAddress -like "*123 Building*") -and (UserAccountControl -ne 'AccountDisabled, NormalAccount'))}

###

Powershell and AD User Information

Today I was handed a CSV list of AD users and asked to return the list with an “Enabled” column to represent wether a user was enabled or disabled in Active Directory. I used PowerShell to quickly whip this up. For future reference, here is the PowerShell that will take a CSV with a “User” column (that contains the user account name) and generates another CSV with the AD properties desired (in this case enabled, sam account name, and email address).


Import-CSV -Path .\mailbox_access_list.csv | ForEach-Object {
Get-ADUser ($_.User) -Properties Enabled,EmailAddress,SamAccountName | select Enabled,EmailAddress,SamAccountName
} | Export-CSV .\results.csv -NoTypeInformation

### - Pablo Aizpiri

Also, for those times when you quickly need to get users in a AD group using PowerShell…


Get-ADGroupMember [AD Group Name] | select {"[Domain Name]\" + $_.samAccountName}

### - Pablo Aizpiri

Getting emails from a group:


Get-ADGroupMember [AD Group Name] | ForEach-Object {
Get-ADUser ($_.samAccountName) -Properties EmailAddress | select EmailAddress
}

### - Pablo Aizpiri

AD Linked Server Query 1000 Row/Page Limit

Sometimes, you need to dump Active Directory data into a table or view in SQL Server. It is preferable to use a programming interface to do this or tools such as PowerShell. It is easier, less problematic, provides access to array values, and you don’t have to deal with the page limit. However, sometimes you need to do this from SQL Server.

The problem doing this with SQL Server (other than not having access to multi-value properties, dealing with bitwise operations in SQL, and dealing with fragile queries) is that you normally have a row/page limit set on Active Directory of 1000. This can be changed, but it is usually not an option.

Here I provide a SQL solution to get around the 1000 page limit. It generated a temp table that can be queried after retrieving the results from AD. It is not the most elegant, but it works fairly well in only about 20 lines of code. In this case, I’m retrieving all users. Note that a limitation to this query is having over 1000 users whose name (cn) start with the same letter. Usually this is not the case, but if that’s a problem this can be a good jump start.



DECLARE @SYMBOLS AS VARCHAR(128); DECLARE @query VARCHAR(MAX); DECLARE @ADfields VARCHAR(MAX);
SET @SYMBOLS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; -- AD Search character prefixes used to partition search. (1000 page limit). Below, AD fields to retrieve:
SET @ADfields = 'cn, sAMAccountName, accountExpires, pwdLastSet, userAccountControl, ADsPath, lockouttime, manager, mail, createTimeStamp, employeeID, lastLogon, co, l';
SET @query = 'SELECT * INTO ##tmpAD FROM (';

-- Get each character and for each character construct and AD query:
WITH nmTbl AS (SELECT TOP (LEN(@SYMBOLS)) Idx = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects ORDER BY Idx)
SELECT @query = @query + 'SELECT * FROM OPENQUERY(ADSI, ''SELECT ' + @ADfields + ' FROM ''''LDAP://DC=newfield,DC=com''''   
WHERE objectCategory=''''Person'''' AND (cn = ''''' + SUBSTRING(@SYMBOLS, nmTbl.Idx, 1) + '*'''') AND (objectClass = ''''user'''' OR objectClass = ''''contact'''')'')
UNION
' FROM nmTbl;

-- Finish generating query string:
SELECT @query = LEFT(@query, LEN(@query) - CHARINDEX(REVERSE('UNION'), REVERSE(@query)) - 4) + ') AS qry'

-- Remove temp table if existing before running:
IF OBJECT_ID('tempdb.dbo.##tmpAD', 'U') IS NOT NULL 
	DROP TABLE ##tmpAD; 
EXECUTE(@query)

SELECT * FROM ##tmpAD


.NET PixyCam Library

UPDATE 12-22-2017: I’ve gotten requests for help on getting this project building from multiple readers. I have updated the project to support newer target platform and provided a more comprehensive build guide here: https://github.com/JhottMaster/PixyUSBdotNET/blob/master/README.md

Not too long ago I saw a Charmed Lab’s PixyCam project on Kickstarter, and I decided to contribute.

PixyCam

My PixyCam assembled with Pan/Tilt Mechanism

I was really excited about all the possibilities vision can bring to the maker community. I later received the camera, and I have to admit it’s pretty cool. The PixyCam is basically a camera with a micro controller that can be used to tag and recognize colored objects. It can then send this “digested” data back to a micro controller that normally wouldn’t have the processing power to do object recognition. For example, certain colors can be tagged- “signatures”- and the camera can send back through an I2C connection the coordinates and size of one or more rectangles matching this “signature”. And at 50 FPS, there’s some pretty nifty stuff you could do with it, like a robot that follows a ball.

The camera also has two ports to connect two servos that you can control through the I2C connection as well. This makes it easy to quickly get up and going. After assembling mine with a pan/tilt mechanism, I played around with the standard demo program and had fun seeing it “look around” at whatever I had tagged for it to view. Next, I wanted to use it to try out some other ideas, but I wasn’t ready to use it with a micro controller and work on all the programming that goes along with that. Instead, I wanted to quickly use .NET to play around with it. Much to my dismay, I could not find an easy way to do this. Ironically, my desire for an “easy” route led me to build a .NET library. I wanted one and figured others could use it as well.

I’m still working on it but I put together a Visual Studio solution that includes my C# .NET PixyCam library, and the required C++/C projects (only required to generate the DLLs the .NET library uses).

I also included a sample .NET application that uses the library so that it can be easy to get started. The solution and source code can be found on GitHub: https://github.com/JhottMaster/PixyUSBdotNET/tree/master

A couple notes:

1.) Make sure you set a signature if you want the sample application to work. Just fire up PixyMon and tag a color to use as a signature.

2.) Make sure you are using a cable that works (most recommended is under 3 feet) or sends enough power to the servos. I deal with this a lot and it was super frustrating, until I got a cable that could transmit more power over further distances.

3.) The library is a work in progress, keep that in mind. Read the README file for more info.

 

 

SSIS Database Security

Generic SQL Security

Quick intro on granting SQL rights in general- I’ve found to quickly setup SQL security I normally use the “db_datareader” and “db_datawriter” roles (assuming entity needs write access) but often times I also need them to be able to execute a stored procedure as well. I don’t want to give them much more access so I find it convenient to just create a “db_executer” role I can assign:

CREATE ROLE db_executor
GRANT EXECUTE TO db_executor

 

SSIS Security

Anyway, back to the real subject of the post, SSIS- if you have a job you want someone to run from SSIS, they need to have permissions to the SQL SSIS database. Usually I don’t want to manage individual permissions at the user level in SQL server, so a popular options is to create an AD group and grant that AD group access to the SSIS database as needed. Then one only needs to manage the AD group. More on SSIS security here.

Recently, we had a situation where we had a report that fired off a store procedure that submitted an SSIS package, waited for it to finish, and returned the results. (here’s how) We were using windows authentication which wasn’t optimal because we had to grant that group/users access to the SSIS database- and plus we had to update security in two places, the SQL SSIS database and the SSRS report.

For this situation, creating an AD group that was used by both SSRS and SSIS didn’t make sense because they would both likely diverge at some point, but mostly we didn’t want to provide those users access to log into the SQL database. So instead we created a single user account that the SSRS data source could use to connect to the SQL server- then we can just control access to the report from SSRS security. By the way, one caveat to this method is that the account can’t be SQL account- it has to be Windows authenticated account in order to run the SSIS package[s].

 

Removing SQL Users from SSIS Database

Anyways, after I did that I decided to clean up the database but found I couldn’t delete the users from the SSIS database; I got an error that dropping the user failed:

Msg 27226, Level 16, State 1, Procedure ddl_cleanup_object_permissions, Line 16
The database principal has granted or denied permissions to catalog objects in the database and cannot be dropped.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

I quickly unchecked all the database role memberships but still couldn’t drop the user. I ended up going through a series of steps with some valuable queries that I wanted to share for future debugging. First, I looked at the permissions assigned to the user:

SELECT *,
object_name(major_id) as securable,
user_name(grantor_principal_id) as grantor,
user_name(grantee_principal_id) as grantee
from sys.database_permissions
where grantee_principal_id = user_id('[DOMAIN]\[USERNAME]')

At this point this only showed the “CONNECT” priviledge, which I was able to revoked:

REVOKE CONNECT TO [DOMAIN\USERNAME]

Now as far as I could tell my user didn’t have any rights but I still got the same errors. I should have paid more attention to the error message- particularly the part about a trigger. This is a trigger in the SSIS database that fires off when dropping a user that has SSIS catalog permissions. SSIS has its own permissions layer and those permissions needed to be revoked in order to remove the user. SSIS catalog permission information on users can be found in
[SSISDB].[catalog].[explicit_object_permissions] table.
I found a handy query for querying this information and generating the SQL to revoke the permissions using
catalog.revoke_permission
here:

SELECT TOP 1000 [object_type]
       ,[object_id]
       ,[principal_id]
       ,[permission_type]
       ,[is_deny]
       ,[grantor_id]
    , 'EXEC catalog.revoke_permission @object_type=' + CAST([object_type] AS VARCHAR) 
          + ', @object_id=' + CAST([object_id] AS VARCHAR) 
          + ', @principal_id=' + CAST(principal_id AS VARCHAR) 
          + ', @permission_type=' + CAST(permission_type AS VARCHAR)
   FROM [SSISDB].[catalog].[explicit_object_permissions]
   WHERE principal_id = USER_ID('Your User')

Nobody Cares Why

The other day we had a bug in one of our applications that prevented users from using certain symbols when saving “document” objects in the application. I got a ticket about the bug, so I looked into it and realized when the application was saving the “document” object to the database it would first attempt to write the file on a filer and this is what was failing- the illegal characters were an illegal Windows file name, and when writing to the filer this was failing.

So I call up the user and shared with them the problem- when the document is being saved, the file included is also saved and the characters in the name are illegal file names. “You know- like when you save a file on your desktop and you include a backslash or something”. The user had an “Ah! Yeah, I understand” moment when it immediately made sense with their own experience of using Windows.

Anyway, later I was thinking about why did I do that? Why did I have to explain to the user why their action didn’t work?

Nobody cares why it didn’t work.

Nobody cares about some artificial (as far as the user is concerned) Windows “limitation” on how the file is “supposed” to be named. It should just work.

Usually I am pretty good about thinking from the user experience perspective; yet here I caught myself forgetting for a moment and with developer tunnel-vision. I may care about why it doesn’t work- I need to most of the time- but nobody else does, and they shouldn’t.

On a slightly unrelated note, user interfaces often suffer from developer’s view of the world. I recently heard a quote I liked regarding user interface design: “The user interface is like a joke- if you have to explain it, it’s not that good”.

Executing an SSIS package from Stored Procedure

I recently needed to do this for a report. In theory, the SSIS package was going to run on some sort of schedule, but then it was decided that the user wanted the ability to run the report on-demand. Since the SSIS package didn’t take too long to execute, I decided I would try to run it on-demand. I was using this in an SSRS report, so I had to build a stored procedure to do this.

The key here is that I had to run the SSIS package, wait until the package was done, and then return data. I also didn’t want the package to potentially run indefinitely, and so if a package ran longer than the timeout or the job ran into an issue, the report should return empty.

NOTE: Make sure the user that will run the procedure also has access to run the SSIS package. (This is granted by giving user access to SSIS database; more here)

This is what I came up with which ended up working rather nicely:

-- =============================================
-- Author:		Pablo Aizpiri
-- Create date: 7.15.2015
-- Description:	Runs the SSIS package that returns results
-- results
-- =============================================
ALTER PROCEDURE [dbo].[RefreshAndQueryFromSSIS]
	@TIMEOUT_SECONDS INT = 60 -- Max number of seconds to wait for SP to execute.
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @EXECUTION_ID BIGINT, @OUTPUT_EXECUTION_ID BIGINT

	-- Setup SSIS package to run:
	EXEC SSISDB.CATALOG.CREATE_EXECUTION
						@FOLDER_NAME = '[SSIS Package Folder Name]'
						,@PROJECT_NAME = '[SSIS Project Name'
						,@PACKAGE_NAME = '[SSIS Package .DTSX File Name]'
						,@EXECUTION_ID = @EXECUTION_ID OUTPUT

	-- Run SSIS package:
	EXEC SSISDB.CATALOG.START_EXECUTION @EXECUTION_ID
	SET @OUTPUT_EXECUTION_ID = @EXECUTION_ID;

	DECLARE @STATEID AS INT = 0, @SECONDSRAN AS INT = 0
	-- Waits until package is successfully completed or until max timeout is reached:
	WHILE (@STATEID < 3 OR @STATEID = 5) AND @SECONDSRAN < @TIMEOUT_SECONDS
	BEGIN
		WAITFOR DELAY '00:00:00:5'; -- Give time back to CPU while waiting for timeout
		SELECT @STATEID = status, @SECONDSRAN = DATEDIFF(SECOND, start_time, GETUTCDATE())
		FROM SSISDB.catalog.executions
		WHERE execution_id = @EXECUTION_ID;
	END

	SELECT *
	FROM [Results table here]
	WHERE @STATEID = 7 -- this ensures results are only returned if the package execution was successful.
END
« Older posts

© 2022 Pablo Aizpiri

Theme by Anders NorenUp ↑