Windows Server 2012 R2 – Change Environment Variables

What a source of frustration!! I’m only a lowly BI Developer so figuring this out for myself was made pretty horrible by the seemingly complete lack of documentation from Microsoft.

There are a couple of ways to get to your environment variables in Windows Server 2012.

Way 1:

1: Windows Key > Right-click on This PC and select Properties

2: Click on Advanced system settings on the left

3: Select Environment Variables

Way 2:

1: Windows Key > Type ‘Control Panel’ and open the Control Panel

2: Select User Accounts

3: Select User Accounts (yes… again)

4: Edit button for Environment Variables appears

Happy Hunting!

SSIS package hanging when using transactions

Again, I’m adding loads of data to a table that I want to roll back when a failure occurs, the data gets loaded by day for a number of days, so a ForEach sequence container was used and its Transaction Option is set to ‘Required’

When I was inserting into my destination, for speed, I had the Table Lock checkbox checked, meaning that the insert would obviously lock the table.

So? What’s the problem?

The problem was that after each insert into the table, the Table Lock wasn’t being dropped, causing my whole SSIS package to grind to a halt.

The only way I could see to resolve the issue was either to do the inserts without a transaction, or to remove the Table Lock from the destination. This all worked fine until there was an error in the source data causing one of the records to redirect to an error table, again, which had Table Lock specified. The answer was to remove Table Lock from all tables inside the transaction.