Archive for March 2012

Courion Tip – Target ID names

Until you progress beyond reading my posts, I couldn’t suggest strongly enough that you keep the targetid consistent in name structure and in every place it’s used.
To clarify, you need to enter the targetid 3 places in the workflow , 3 places in the AMM connector, and 2 places in the PMM gateway connector.
There maybe good solid reasons to use different names, but I would start by assuming there isn’t.

Posted March 21, 2012 by mmdmurphy in Courion Tip

Tagged with

Courion Tip – SSIS Package changes

First off – MAKE A COPY OF THE SSIS PACKAGES before you do anything. If possible, implement a versioning system. I like to just copy the folder, and rename the copy to yearmonthdate_package name. For instance, 20120301_Courion_SAP_Import

I can’t give you too many specifics, I will probably re-edit this post several times, but here are my gripes and my solutions to something that is more a matter of opinion than anything else.

Make as many copies of the package as you have target system types, then strip out all the other targets. In other words, if you have one package that imports 8 target types, you should wind up with 8 packages, each dedicated to a particular target type. I rename all of them to match what they are importing. For instance, Courion_SAP_Import, Courion_AS400_Import, etc. If you share your SQL server / SSIS server or DBA with other systems, the Courion at the front will help know it’s your package that’s failing.

Why split it up this way?

Well, for one thing, you can schedule them as appropriate. We import Active Directory every few hours looking for new accounts, but AS/400’s only once a day.

Secondly, debugging and updating. The person who did ours was, admittedly, under a time constraint. So, the resulting package worked just fine, but generated something like 40 warnings. This was a little intimidating for me (didn’t know SSIS very well at the time). By splitting it up, I could look at a few warnings and tackle them one at a time to get rid of the warnings. Also, you don’t want to add a target to just a small part of it, only to find out you have broken ALL of it.

Most of the warnings consisted of ‘data truncation could occur…’ Basically, we were importing 100 characters from a file, and putting it into a 50 character column. If you know your data (see my other post), you should know what the maximum is (50? 100? 2000). To correct this, start by setting your column in the table definition to something larger than or equal to the maximum (for instance, no point in having employee id’s that are 100 characters long, when company rules say they should only be 6 characters), and then looking at the connector information in the package. It might take you a while to track them all down, but it will be worth it as a training exercise & you will have a cleaner system.

Learn what *.dtsconfig files are about, and use them!!  We didn’t know how to test our code in our test system, then implement it in production without making fairly big changes to the code. This is the answer.

Your test database table structure must match your production – besides being just plain common sense, good practice and all that – since all you should be editing is the dtsconfig file, your package will fail if the tables are different (or work in an unpredictable way)

Posted March 21, 2012 by mmdmurphy in Courion Tip

Tagged with

Courion Tip – Getting Started: Know your data

Know your data.

Let’s start with the basics. Courion (to me) isn’t a solution, it’s a solution framework. So, it (probably) doesn’t have canned answers for what you are trying to do, you have to come up with them yourself. I’ve had requests to base things on the user’s department number – only to find out that HR was creating new department numbers on a regular basis, but not letting anyone know. So, after a while, processes failed. The decision was made to base those same things on the state an employee was in, and we found that over 60% of the user’s didn’t have that populated.

IdentityMap (TM, courion, all that)

This is one of those super critical tables in courion that you really need to understand. It basically ties all the accounts that a user has together. Usually, it’s 3 columns – side note, seems like it’s grown A LOT since I wrote this. But the main 3 columns are still ProfileUID, TargetID, and UserName.


This is what ties all the user accounts together. You really really need this column, it needs to be unique to each employee, and it needs to exist everywhere. I was at one company where everyone had employee ID’s to log in, and it was already set on every system. No firstname  lastname, or first initial lastname, combinations!! I was A340359 there and everywhere. This made our implementation a dream!! However, at another place, this wasn’t so. Everyone had an employeeID, but no one knew what it was and it wasn’t set on all the target systems.

At this point, you might be pondering if employee id’s are set on your SAP system, or your AS/400’s or …. Find out.  If your systems don’t have an employee id on them, and you’re thinking that firstinitial & lastname will do just fine, good luck.  What about Aaron Adams, and Abigale Adams, or even just the first 2 John Smith’s you hire.

When you import data from your target systems, it is a LOT easier to find all records where employee id is ‘888944’ than it is to determine if that record is from John Smith or Jonathan Smith.

By finding all records in the identitymap where profileuid = some value, you immediately know every system that a uwer has accounts on (that have imports into courion).

Know how long your employee id’s are, and what constitutes a valid employee id. For instance, if all employee id’s are 6 characters long, you should know that. (This also gives you options – perhaps the user accounts you don’t want to touch should be more than 6 characters?). Know how this column is defined in the IdentityMap table. (it’s probably varchar(50), just cos that’s what SQL defaults to). Don’t change it yet, just know what’s what.


This is the user name on the target system. These will probably be all over the map, and here’s where your users are probably set up as first initial last name…  Hopefully, you have naming standards set up on all of your target systems. Employee ID’s pay off well here, because if you are importing jsmith and jsmith1 how do you know which user is responsible for that account??? If you have employee id’s in place, it is a resolvable situation since you would be keying off of the employee id, not the user’s name on that system.


These are probably carved in stone at this point, but if you’re just getting started, stop and think!!  There are plenty of places that this information is used. I strongly recommend that you use target id’s that make sense, mean something to end users, and convey information. For instance, SAP_ABC_110_T tells you right up front that it’s an SAP system, it’s your ABC box, client 110, and it’s a test system.    The target id will come into play when configuring workflow targets, configuring SSIS import processes,  automatically generating emails that go to the end user, and potentially other places (identitymap selection screen in the workflows themselves).

To Do’s

Date time stamp The only change I might recommend here is to add a date time stamp column to the identitymap.  That way, you will be able to keep track of when a user was given an account on a particular system. Auditors will love you for it, and it can help in debugging (for instance, if you search for all entries in the idenitymap for a given target id sorted by the date time stamp, you’d quickly know when the last time a user was added, or when the last time the process worked successfully)

Data cleanup – Before too long, you’ll find that your system is great at importing data about new users on systems, but not so good at removing information. As an example, let’s say you have an SAP account on our SAP_ABC_110_T system today. Next month, your account gets removed (and, let me digress – if ALL of that were to be done inside of courion (adding the account and then deleting it – then it will keep track of the changes, and you need not worry about it. That’s one of the beauties of the identity map). Your identitymap will still show an entry for that system, and you will keep getting password change failure emails every time you change your password.

I would strongly suggest that you encourage the owners of the target systems to send you information about not only ADDITIONS, but also account DELETIONS so that you can avoid this. Why not just delete all the entries that are not in the newest feed? Because, if you are not careful, and the feed fails, you will delete ALL the records for that target system.

Update…. I thought I would throw this in as an example of how not to do something. And note, it’s a real life example – I didn’t make this up!
CREATE TABLE [dbo].[Staging_AD](
[objectGUID] [nvarchar](2500) NULL,
[sAMAccountName] [nvarchar](2500) NULL,
[GivenName] [nvarchar](2500) NULL,
[sn] [nvarchar](2500) NULL,
[mail] [nvarchar](2500) NULL,
[employeeID] [nvarchar](2500) NULL,
[employeeType] [nvarchar](2500) NULL,
[pwdLastSet] [nvarchar](2500) NULL,
[description] [nvarchar](2500) NULL,
[displayName] [nvarchar](2500) NULL,
[department] [nvarchar](2500) NULL,
[streetAddress] [nvarchar](2500) NULL,
[l] [nvarchar](2500) NULL,
[st] [nvarchar](2500) NULL,
[postalCode] [nvarchar](2500) NULL,
[telephoneNumber] [nvarchar](2500) NULL,
[facsimileTelephoneNumber] [nvarchar](2500) NULL,
[memberof] [nvarchar](2500) NULL,
[distinguishedName] [nvarchar](2500) NULL,
[modifyTimeStamp] [nvarchar](2500) NULL,
[useraccountcontrol] [nvarchar](2500) NULL,
[accountExpires] [nvarchar](2500) NULL,
[Disabled] [nvarchar](2500) NULL

Why on earth you would want to store Disabled (a boolean value) as nvarchar(2500) is beyond me. In addition, all the timestamps are also nvarchar(2500). This shows a complete dis-regard for the nature of the data.

Posted March 21, 2012 by mmdmurphy in Courion Tip

Tagged with

Courion Tip – Getting Started: Backup

I have in mind to do a series of these, but let’s start with the basics.

The first thing I think you should do as a new courion admin would be to make sure you have backups. Backups of what?


cfgfile.db & log files

The courion support site has a batch file that you can download and set up on your system. Start with that. Basically, it stops all the services, copies the log files, and restarts the services. Modify it to also make a copy of cfgfile.db before it restarts the services. I have mine set to make 26 backups, if you run it once a week that comes out to 6 months worth of potential ‘roll backs’ you can do. More is better. Oh, and while we are using it for something other than it was originally intended for, you should probably include the archive folder (its under your courion folder).

If you want to get really fancy, you can download the command line version of 7-zip and have it automatically compress the files – but, personally, I didn’t bother.

Schedule it to run in your test environment (you DO have a test environment, don’t you?) once a week or more until you are comfortable, then I would schedule it in production for once a week.  I ALSO manually run it right before making any major changes, so I’d put a shortcut to it on the ‘all users’ desktop on that machine. Remote desktop is your friend.

What is the cfgfile.db? My understanding is that it is a berkeley database (ie, non-relational) encrypted with the name of the server you are running on. If you make changes to the workflows that you MUST get back, it can be the thing that saves you.

SSIS Packages

The next thing I would make a copy of would be the SSIS packages that courion (or someone) has set up. That would consist of simply logging into your SQL server server, not the management console, or anything, just a straight remote desktop, and making a copy of the packages. If you have some sort of versioning system, you are lucky. I would still do it anyway. If you are not familiar with SSIS, I would recommend you take a class in it.  Not SQL Server. (well, if you can talk your boss into it, you should take a course in both SQL server AND SSIS. However, learning things like Merge Replication won’t help you so much with Courion)

Posted March 21, 2012 by mmdmurphy in Courion Tip

Tagged with