Archive for June 2012

SSD Drives

Bought a 120 gig SSD drive, what a pain!

First off, you really ought to run ‘chkdsk /f /r’ on your drive before you begin, and reboot. I wish I would have.

Secondly, I am using ‘EaseUS Partition Master 9.1.1 Home Edition’ to manage partitions. Could have used gparted, but didn’t. No reason.

During the process, I would use CCleaner to

  • bring up a list of apps that are installed
  • make a list of 3 or so
  • uninstall those 3
  • use CCleaner to do a registry check
  • reboot – occasionally doing chkdsk /r /f and/or resize partitions
  • reinstall latest version of those applications

(Of course, I also moved data files and stuff at the same time, not like I was resizing it in tiny bits)

Worth it when I was done? HELL YES.

I am currently considering: – free for home use, if you make less than a 4 gig ramdisk. I have 18 gig RAM, and am willing to put 4 into this…


Update – I tried the ram disk, and ran into issues booting. Sorry, I don’t have the details anymore, but I have shelved it for now.


Posted June 24, 2012 by mmdmurphy in freeware, tips, Uncategorized, windows

Tagged with

Own Cloud notes,_installation_and_setup

Moving ownCloud data store:

Posted June 18, 2012 by mmdmurphy in freeware, Internet, Linux

Tagged with

Courion Tip – LDAP/AD Queries

I have working SSIS packages that do an LDAP bind and pull in user information directly. I want to post the *.dtsx package here, however, I need to make very sure that no confidential information gets uploaded.

Also, it’s entirely possible that it won’t work when I get done. I will copy the *.dtsx to a virtual machine, and sanitize it there. All of which is to say, “stay tuned”.

Update – I finally have these cleaned enough where I am POSITIVE there is no confidential information. Unfortunately, wordpress only allows certain file types. So, I renamed it from RAW_AD.dtsx to RAW_AD.dtsx.docx (click here to download it).

Once you get it up and running, you might need this link to modify it for your needs.

A brief walk – thru (and, NO, these are not best practices – I’m just trying to get your system to collect information. You should tweak it!!)

  • Create a domain admin level account called courion, with a password of ‘password’.
  • Create a database called Courion (unless, of course, you already have one), and give the user courion complete access to it.
  • Start up Business Intellegence Development Studio.
  • File, New, Project, “Integration Services Project”
  • Give it a name that matches your standards, and a file location that makes sense. For me, I used “D:\SSIS Packages” and called the project “RAW_AD” – since it will be doing an import of Active Directory data and minimal manipulation of the data.
  • On the far right side, right mouse click on the words “SSIS Packages”, and choose “Add Existing Package”
    • Change the package location from “SQL Server” to “File System”
    • Click on the 3 “dots” to browse to the file you downloaded from this site (after you took the .docx off). Note that it will copy it to your new solution, so that you don’t need to worry about saving the original – it won’t be touched.
    • Click on Open, and OK.
    • You will probably get an error message about something not being in the right cryptographic state, don’t worry about this now.
  • Double click on the “RAW_AD.dtsx” and it should open up.
  • At the bottom of the screen, under “Connection Managers”, you should see 2 – “Courion Database” and “SMTP Connection Manager”.  For now, ignore the SMTP connection.
    • Double click on the “Courion Database” connection manager.
    • Change the name of the SQL Server to match your environment.
    • Choose whichever authentication matches your environment, but I’d suggest “Use Windows Authentication” – for now.
    • Choose your Courion database under “Connect to Database”. If you can’t choose it from the drop down box, then you probably have the server name incorrect, or you have chosen the wrong instance.
    • Click on “Test Connection” – if the connection fails, then the entire thing will not work. You must fix this.
    • Click on “OK”
  • Repeat, if you want, with the SMTP connector. (though it’s configuration is much simpler)
  • Create the required tables:
    • RAW_AD_temp: Right click on the box that says “Drop and recreate RAW_AD_temp”, and select “Execute Task”. While this isn’t strictly necessary, you’re probably seeing some error messages. By creating this table, the errors/warnings will go away.
    • RAW_AD. Download Create RAW_AD.sql.Again, to get it to upload, I had to rename it, putting a “.docx” at the end of the name.  Open it with any text editor, and then copy it’s contents into your SQL server management studio. Execute the sql statement, and you should be all set creating this table. You might want to create this table on your own. However, since the column names and data types might not match what I chose, you might generate errors. Save that for a future experiment.

At this point, you should probably save your work, and exit out of BIDS (Business Intelligence Development Studio). Then go back into BIDS, and open your project. Why? Mostly, to clear out any error messages that you see and verify that any existing errors/warnings are ‘real’ ones, and not just cached/queued ones.

If you don’t see any error messages, then it’s probably time to test your package. You might wonder about the greyed out boxes – some are for email notifications, and the others are ideas I haven’t fleshed out completely. Perhaps I will post updates to this, but for now, since I am assuming you are new to this, please ignore them.

To run the SSIS package, click on the “Debug” menu, then “Start Debugging” menu item. As each step of the process completes, it should turn green for success, or red for failure. Please read any pop-ups, as they should help you debug the SSIS package.

A few final notes:

  • Data types and data length. There are several places that modifications need to be made, and I have been very careful to avoid generating errors and/or warnings.
    • RAW_AD and RAW_AD_temp tables. Don’t forget that the temp table gets rebuilt on the fly, so you need to edit the script, not just the table.
    • The “Inputs and Outputs” of the “Script Transformation Editor”
    • The vbscript itself
  • Rebuilding the vbscript – to rebuild the vbscript that does the ldap bind:
    • Double click on the box that says “RAW_AD temp – Dataflow”
    • Double click on the box that says “RAW_AD Query Script”
    • Click on “Edit Script”
    • A new window should open, called “ssisscript – Integration Services Script Component”
    • In the upper right of the screen, right mouse click on “ad_query_vbs” and select “Rebuild”
    • Click on “File” and “Save All”
    • Close out this window

There is certainly room for improvement. For instance, instead of dropping all the data from the RAW_AD table every time, a slowly changing dimension could be used. The authentication for SQL Server could be stored in a variable (in a configuration file) making the code portable. And, of course, what data is collected could be changed.

Additional notes – SQL server supports a ‘checksum’ calculation, for instance : This makes a WHOLE lot more sense for checking to see if a user’s information has been changed than a HUGE query. That, coupled with triggers, will ensure that the information is up to date.

Posted June 7, 2012 by mmdmurphy in Courion Tip

Tagged with

Courion Tip – Before Courion Arrives – Hardware and Software   Leave a comment

Basically, you don’t want to scramble ordering software and hardware after Courion has arrived at your door. Why not get ready first?

For starters, please see my other post You obviously need servers – some for SQL Server, and some for Courion, but I can’t give you a simple summary of what you need. It depends on your environment. However, you probably can start off with a virtual machine for SQL Server and a virtual machine for Courion. Try to get that approved and set up as soon as you can. Don’t forget the ability to take snapshots of the VM’s – and I’d take them during the install process so you can always roll back.

Mostly, you need the Business Intellegence Development Studio and the Microsoft SQL Server Administrator tool (sorry, that’s from memory and may only be close to right). You need to get your feet wet with both products. Don’t be shy – courion shouldn’t be there yet, you should be taking snapshots, and you should be ready to loose everything you’ve done – don’t make any permanent changes. (also, make sure you have copies of any SSIS packages you make – preferably in a central place like Sharepoint or VSS or even a network share)

Hardware – Nope, not servers. Your desktop. You will need the biggest monitor you can get. No kidding, it’s not just ‘nice to have’. There are certain Courion configuration screens (specifically, Active Directory Unique Resource Data) that are HUGE and it’s impossible to see the entire thing at once without a large, wide, monitor. It might be a hard sell to management – but it will be worth it. Also, you don’t need the highest performance – you aren’t playing games. Size is the biggest issue.

I would suggest Windows 7, 64 bit, with 8 gig or more of ram. Ideally, you’d like to run a virtual machine on your computer, with SSIS and SQL server, and maybe Couron installed on it. It’s nice to be able to kick around something ‘in private’. However, you’re not trying to replace your developement system, just augment it.

You might consider getting Microsoft Visio and a product like Microsoft Project. It’s good to be able to map out SSIS packages in Visio, and a project management system is obviously a good idea.

(by the way, you don’t need a full blown copy of Microsoft Visual Studio. I wouldn’t bother, but that’s not to say I know the nature of your environment and project)

Posted June 1, 2012 by mmdmurphy in Courion Tip

Tagged with

Courion Tip – Before Courion arrives – SSIS and datafeeds   1 comment

Lessons learned from the installs I have seen…. There’s a lot you need to know how to do, and by doing some of the work ahead of time, Courion can hopefully focus on more advanced issues. By doing these things, you learn the skills you need.

Send (or be sent) to SQL Server SSIS training. NOT SQL Server training, SSIS training. SQL Server and SSIS are KEY to Courion. Why not SQL Server training? Because – unless you are going to be your own DBA – you really don’t need to know how to install SQL Server, nor replication technologies, etc.

Take a VBScripting class. Courion supports both Javascript and VBscript, so this is more of a personal preference. But you need to know one of them – maybe both.

Deciede if you need to support unicharacter – you probably do.

Write an SSIS package that imports a flat file to a SQL Server table. It would be best if you actually import a flat file from your HR system, and populates a real table. Name the destination table RAW_something. “We” get our data from ADP, so I call the table we import to RAW_ADP. Do not “pick and choose” which data you will need, you can do that later. Be sure to import dates as dates, strings as strings. Arrange to have the file generated on a daily basis, and schedule your package to run daily – obviously, after the export happens.

Write an SSIS package that imports user information from Active Directory to a SQL Server table, using VBscript. Pull in as much information as you need. I import mine into RAW_AD table, AD being Active Directory of course. Schedule this to run on a daily basis, but be aware that you might wind up running more often than that.

I hope to scrub the packages I have and upload them, but I cannot be sure I will get approval for this.

If you get these done, you will be a LONG way to understanding SQL Server, SSIS and being able to understand the Courion person when they come out. When the consultant arrives on your doorstep, insist that they base populating the identitymap off of your RAW_tables. They might not readily agree, but if your process is sound, there is no reason not to. They will probably have to modify your package to point to whatever database Courion is actually going to use, that’s fine. They MIGHT want to modify it to remove users that aren’t in the identitymap – don’t let them. Emphasis that this is RAW data, that they should use this as a data source for thier process.

One final comment about SSIS and Courion – And this is going to sound like a personal preference, I don’t think it is – Stress to the consultant that you want SEPERATE SSIS packages for just about everything.

See my other post

Posted June 1, 2012 by mmdmurphy in Courion Tip, SQL

Tagged with