Windows Server 2012 R2 – Change Environment Variables

Standard

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

SSIS_EnvVar4

2: Click on Advanced system settings on the left

SSIS_EnvVar5

3: Select Environment Variables

SSIS_EnvVar6.png

Way 2:

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

2: Select User Accounts

ControlPanel

3: Select User Accounts (yes… again)

ControlPanel2

4: Edit button for Environment Variables appears

ControlPane3

Happy Hunting!

Advertisements

SSIS package hanging when using transactions

Standard

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’

TransactionOption

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.

TableLock

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.