SSIS trickery for Live@Edu

Feeling quite pleased with this so thought it was worth a post 🙂

I’ve been trying to sort out a solution for getting accounts onto Live@Edu recently, usually you’d just say ILM \ GALSync and be done with it, but in our case things are slightly different. We don’t have a single intake like colleges do, in adult education new enrolments and courses are happening every week, which makes things fun for getting all the new users onboard! With ILM we’d still have to export from our MIS to AD each week then let ILM do its stuff.

That didn’t seem the best way for us as it still relied on a CSV export at the start anyway so I thought about going down a different road and ended up looking at the PowerShell CSV Parser. Idea being to pull the new enrolments out through SQL, automatically format the required CSVs and import into Live@Edu from there. That decided it was a matter of automating this as much as possible. To find the new enrolments a mismatch query runs to find students not in the user details table we’ve had set up in our MIS and at that point generates the required information. I’ve made a package in SSIS to run all the steps as below…

  • run data mismatch query
  • format results into CSV columns as required by the CSV parser
  • append date onto the CSV and place into folder
  • update the main user details table with the new users
  • do a row count on both tables to find the number of new users and total number of users present
  • run the PowerShell command to upload the new users to Live@Edu
  • send an email to the IT departmental email address with details of number of users added, total number of users and the PowerShell log for that day

A few tips I found along the way while working with SSIS for pretty much the first time…

  • variables and expressions are very very handy, one bit of code that was very nice was to put the date on the end of the filename in DDMMYY format

“c:\\importfile” + “_” + RIGHT(“0” +  (DT_WSTR, 2) DAY(  GETDATE() ), 2 ) + RIGHT( “0” + (DT_WSTR, 2) MONTH(  GETDATE() ), 2 ) + (DT_WSTR, 4) YEAR( GETDATE() ) + “.csv”

Note the double slash on the folder path!

  • grabbing the rowcount was simple enough; create a new variable in the scope of the project then using result set map it to the variable. I found setting Result Name to 0 avoided any issues, in some circumstances setting it to anything else will give an error.  What was a nuisance was finding the right bit of code to change the variable from an integer (must be of int type to read the rowcount) to a string (required for the body of the message in SendMail). The magic function was…

(DT_WSTR, 10) @[User::yourvariablename]

To count the rows from a table use this…

SELECT COUNT(*) FROM Sometable

Also if you want to put multiple carriage returns in the SendMail body in an expression use quote marks to start the string then put your carriage returns and carry on from there

  • running PowerShell commands can be a common gotcha if you don’t set the remote execution policy as one of your Execute Process Tasks. Make a Task then put this in the boxes…

Executable: c:\windows\system32\windowspowershell\v1.0\powershell.exe
Arguments: Set-ExecutionPolicy RemoteSigned

After the script runs (use the argument as path to your .ps1 file) I’ve set the policy back to AllSigned (default) just to be on the safe side.

A nice bit of code for testing whether PowerShell works is this basic messagebox function…

[System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
[System.Windows.Forms.MessageBox]::Show("Hello")

A few useful links I found (for reference)…

Create dynamic subjects \ messages for the Send Mail task
http://support.microsoft.com/kb/906547

Send email from SSIS
http://www.mssqltips.com/tip.asp?tip=1731
http://msdn.microsoft.com/en-us/library/ms188441.aspx

Using Result Sets with Variables
http://msdn.microsoft.com/en-us/library/ms141689.aspx
http://msdn.microsoft.com/en-us/library/cc280492.aspx

It’s a nice sight to see all the boxes lighting up green then the email pops through a few minutes later, result! I’m thinking of running this once a week as that’s the same time we’re going to be adding courses on so the process should slot together quite nicely…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: