Powershell and Lotus Notes pt2

To continue my journey (PS and LN pt1) in PS scripting on Lotus Notes, I wanted to see if it is possible to manipulate groups members in LN. Well, it is possible, and I will show you how I did it.

As you remeber from my last post, If you want to connect to Lotus Notes via PS, you must start powershell or PS ISE in 32bit mode.

#open powershell in 32bit mode
#Start-Process $Env:WINDIR\SysWOW64\WindowsPowerShell\v1.0\powershell.exe
#or ISE
#Start-Process $Env:WINDIR\SysWOW64\WindowsPowerShell\v1.0\powershell_ise.exe
if([Environment]::Is64BitProcess -eq $true)
                    {
                    write-output "64bit NO GO" 
                    BREAK
                    } 
            else {
                        write-output "32bit OK"
                        } #because you have a 64-bit PowerShell

We are going to try this on a test group called #LN_TEST_GRP and the user we are going to add/remove from this group is CN=User TEST/O=LNDS

#Parameters
$strUserView = '$VIMGroups'  #System name for groups view in names.nsf
$DomServer = "SERVER/LN" 
$DomDBPath = "names.nsf" #mailbox that contains users, groups.. etc..
$pwd4NotesDB = "Passw0rd" 

$User = "CN=User TEST/O=LNDS"
$LNGroup = "#LN_TEST_GRP"
$Insert = 0 #1 for insert user
$Delete = 0 #1 for delete user
$Array = "" #Set Array to empty

#This part is for testing insert/delete - manually set values
$delete=0
$Insert=0
#

$DomSession = New-Object -ComObject Lotus.NotesSession #Use LN COM class
$DomSession.Initialize($pwd4NotesDB) #This is when Lotus asks for your password when you open it
$DomDatabase = $DomSession.GetDatabase($DomServer,$DomDBPath) #Initialize Database
$DomGroupView  = $DomDatabase.GetView($strGroupView) #Initialize View

$DomGrp = $DomGroupView.GetDocumentByKey("$LNGroup") #Get group from Group List
#Now we save members to Array and prepare MaxArry to handle For loop
$userGrp = $DomGrp.GetFirstItem("members") #For text Append
$Array = @($userGrp.Values) #GetValues and save to array
$MaxArray = $Array | measure -Maximum #GetMaxOf Array
$MaxArray = $MaxArray.Count #GetMaxOf Array

Now, when we got this sorted, the next step is manipulating group members. First we will add the user, then we are going to remove it from group.

The process of insert is very straightforward, If user is not on the member list, add the user to the end of that same list.

#INSERT USER INTO GROUP DOCUMENT
IF ($Insert -eq 1) # IF INSERT IS 1 
{
        IF (($userGrp.values -contains $User))
            {
                "******************User Exists - EXIT****************"
            }
        else
            {
                "******************User NotExist - INS****************"
                IF (($User -ne $nul) -and ($LNGroup -ne $nul))
                    {
                    $userGrp.AppendToTextList($User) #Add user to end of members list
                    $DomGrp.Save('False','True') #Save Group
                    }
            }
}

The process of deleting user from members list is little more complicated than insert. We input members to array, find user that needs to be deleted and replace the user with “”, then we cleanup the array, and finally we replace members value in LN group document with that new array.

#REMOVE USER FROM GROUP DOCUMENT
IF ($Delete-eq 1) # IF DELETE IS 1 
{    
        
        IF (($userGrp.values -contains $User))
            {
                "*******************User exists - DEL****************"
               IF (($User -ne $nul) -and ($LNGroup -ne $nul))
                    {

                            for ($i=0; $i -lt $MaxArray; $i++) #FOR Loop
                            {
                                IF ($array[$i] -eq $User) #IF user is in the list
                                {
                                $array[$i] = "" #Set "" for that user
                                $Array = $Array | Where { -not [string]::IsNullOrWhiteSpace($_) }     #Clean Array of blank spaces
                                }
                            }
                            #$Array
                            $Array=$Array.Trim() #Array Trim
                            $DomGrp.ReplaceItemValue("Members",$Array) #Replace Members list with our new array without the specific user
                            $DomGrp.Save('False','True') #Save Group document
                    } 
            }
        else
            {
                
                   "*******************User NotExists - EXIT****************"
   
            }
  }

So, this should cover the basics in adding and removing users from Lotus Notes.

Good Luck

Powershell and Lotus Notes

As you might know, Powershell works best with other Microsoft products, like MS Exchange.

Now what happens when you want to connect PS to some non Microsoft products? In my case that was Lotus Notes. The collaboration giant. As you probably know, Lotus has its own LotusScritp language, with whom you may create Lotus databases, agents, users, etc… With it you can do almost anything in Lotus.
Since I like to test and experiment with Powershell, and one of my projects was Powershell script and Exchange, Self-service user portal, I decided to play with Powershell and Lotus Notes. And I must say, I works.

First of all, If you want to connect to Lotus Notes via PS, you must start powershell or PS ISE in 32bit mode.

#open powershell in 32bit mode
#Start-Process $Env:WINDIR\SysWOW64\WindowsPowerShell\v1.0\powershell.exe
#or ISE
#Start-Process $Env:WINDIR\SysWOW64\WindowsPowerShell\v1.0\powershell_ise.exe
if([Environment]::Is64BitProcess -eq $true)
                    {
                    write-output "64bit NO GO" 
                    BREAK
                    } 
            else {
                        write-output "32bit OK"
                        } #because you have a 64-bit PowerShell

Once you open the PS or ISE (I’m more of ISE or Visual Code person), you can start connecting to Lotus Notes.

One of my tasks, was to extract an HTML attachment which was sent everyday by our backup system. But with this code, will go through all mail documents in Inbox, and for those that have attachment, it will create a folder for every sender and create date subfolder with attachments in it.

#Parameters
$strUserView = '$Inbox'  
$DomServer = "SERVER/LN" 
$DomDBPath = "mail\user.nsf" 
$pwd4NotesDB = "Passw0rd" 
$ipPath2Export = "C:\Temp"

$DomSession = New-Object -ComObject Lotus.NotesSession #Use LN COM class
$DomSession.Initialize($pwd4NotesDB) #This is when Lotus asks for your password when you open it
$DomDatabase = $DomSession.GetDatabase($DomServer,$DomDBPath) #Initialize Database
$DomView  = $DomDatabase.GetView($strUserView) #Initialize View

Since the script has to go through all the mail, we need the loop.

$Counterf = $DomView.GetFirstDocument() #Define first document in view
While ($CounterF -ne $nul) {

$DomNexDocument = $DomView.GetNextDocument($CounterF) #Define Next doc

#Define some fields
$DomeLoopSubject = $CounterF.GetItemValue("Subject") #GetSubject
$DomeLoopFrom = $CounterF.GetItemValue("From") #GetFrom
$DomeLoopDate = $Counterf.GetItemValue("DeliveredDate")
$DomeLoopDate = '{0:yyyyMMdd}' -f $DomeLoopDate #GetDateandFormat

#for folder name we need to clean FROM
$DomeLoopFrom=$DomeLoopFrom.Replace("CN=","")
$DomeLoopFrom=$DomeLoopFrom.Replace("/O=LN","")
$DomeLoopFrom=$DomeLoopFrom.Replace("<","")
$DomeLoopFrom=$DomeLoopFrom.Replace(">","")
#$DomeLoopFrom=$DomeLoopFrom.Replace("@","")
$DomeLoopFrom=$DomeLoopFrom.Replace("\","")
$DomeLoopFrom=$DomeLoopFrom.Replace("/","")
$DomeLoopFrom=$DomeLoopFrom.Replace("`"","")

IF ($Counterf.HasEmbedded -eq "True") #IF attachment exists
    {
      $AttachItem = $Counterf.GetFirstItem("Body") #Get all attachments     
        
       Foreach($A in $attachItem.EmbeddedObjects)
        {
            $DomAttachSavePath = "$ipPath2Export"
            $DOmAttachSavePath = $DOmAttachSavePath+$DomeLoopFrom 
            $FILENAME=$A.Name
            $DomAttachSavePath=$DomAttachSavePath+"\"+$DomeLoopDate   
            Write-Output "Possible path  $DomAttachSavePath"
            New-Item -ItemType Directory -Force -Path $DomAttachSavePath >$null
            $Extractto = $DomAttachSavePath+"\"+$FILENAME       
            $A.EXTRACTFILE($Extractto) 
      }

    }
$CounterF = $DomNexDocument #Raise counter
}

So, this is how I managed to export all attachments from my Inbox in Lotus Notes.

Good Luck

SQL- Creating linked server to AS400

Have you ever tried to connect to AS400. It’s been a hassle for me. But finally I got it to work, so let me share little of my wisdom with you.

First create linked server, then change options to it.

EXEC master.dbo.sp_addlinkedserver @server = N’AS400′, @srvproduct=N’i520′, @provider=N’IBMDA400′, @datasrc=N’AS400′, @provstr=N’User Id=USER;Password=PWD;Default Collection=DAT;’

Change option:

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N’IBMDA400′, N’AllowInProcess’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’IBMDA400′, N’NonTransactedUpdates’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’IBMDA400′, N’SqlServerLIKE’, 1
GO
EXEC master.dbo.sp_serveroption @server=N’ASUHRP12′, @optname=N’collation compatible’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ASUHRP12′, @optname=N’rpc’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ASUHRP12′, @optname=N’rpc out’, @optvalue=N’true’
GO

So, you should now have functional linked server. Only, in my case its very slow, so it could be in your case to.

Good Luck

The Love between IBM iSeries Access and SQL 2012

Ok, so let’s start with my first blog.
Couple of days ago, I’ve been given a task to migrate some tables from AS400 to SQL.
So first of all, you would need a connector (provider) to IBM AS400 structure. So that connector comes with IBM iSeries Access software. After quick and “select All option” installation, I thought this is it.
I started SQL Import and Export Wizard to test things out. But the error appeared:
image

After couple of minutes, I found that the .NET config file had been corrupted. Searching through .NET config, I found the problem. In file “C:WindowsMicrosoft.NETFrameworkv4.0.30319Configmachine.config” there was a unclosed line
<system.data>
<DbProviderFactories>
<add name=”IBM DB2 for i5/OS .NET Provider” invariant=”IBM.Data.DB2.iSeries” description=”.NET Framework Data Provider for i5/OS” type=”IBM.Data.DB2.iSeries.iDB2Factory, IBM.Data.DB2.iSeries, Version=12.0.0.0, Culture=neutral, PublicKeyToken=9cdb2ebfb1f93a26″ />
</DbProviderFactories>
   <DbProviderFactories/>  —-delete  this line
</system.data>
Of course you’ll probably have problems of saving that protected file. So, my advice, save that file on desktop, edit it, and then copy it back to original location.

After that, the Import Export worked as a charm. Now, next step is to create a DTX package, and schedule it to transfer data. SQL 2012 does not have Business Intelligence, but SQL Server Data Tools (further in text SSDT).

Start SSDT, create a project, create Control Flow, add Connection Managers to SQL server and to AS400.

But, I’ve stumbled on a problem here.
When working and testing DTX package from SSDT, everything works, but for some reason when you import that package into Integration Services, and start it, the password for IBM AS400 connector is missing.
Solution to this problem is:

Start SSDT with “Run as Administrator” option, design package, enter username and password into AS400 connection Manager , and select “Allow saving password”.
image

Save package. When importing to Integration 
Services you must select option “Rely on server storage and roles for access control”!

image

And that about covers the problem I’ve encountered working with these two products.

Enjoy migrating!
Smile