I am looking to automate an import process using powershell.

My goal is to automatically have emails that come into my Outlook 2007 Inbox (XP) get imported into my SQL Server 2005 database. (Windows server 2003)

I also have windows server 2008 and sql server 2008 as an option, but am working thru some install issues with sql 2008 at the moment:(

Is Powershell the best way to do this?

Thanks

I used the following code to read my inbox.  more an exercise in "can I"... Having said that... Is this a onetime thing, or will this be a production resource?  Outlook is a cool little app, but processing mail from your local mail client.  Then using that data a populate a SQL database, bad juju...  Love to help all the same.

$outlook = New-Object -ComObject Outlook.Application

#you will have to grant permission within outlook. 
$mailbox = $outlook.session.folders | ? { $_.Name -eq "your Mailbox name"}  

#lots of GM from here on out... very self explanatory 
($inbox = $mailbox.Folders | ? { $_.name -eq "inbox"}).items | % { what-do-you-want}

-Glenn

Desktop Mail Store.

But if there is a better way of doing it i'm all ears!

Thanks 

This will be an ongoing situation.

At the moment i have to maually setup batch jobs each night to do this, and it is just waaay too much human involvement!

With exchange accepting emails automatically, i want to find a way to continue this process but instead of stopping at the inbox, i want to add one more step and take it into the sql DB.

I dont see why it should be that difficult, its all within the Microsoft family of products. 

Would exchange 2007 offer any advantages?

Why the bad juju?

Thanks 

Yup, dead on Hal, Like I said before, if you’re doing this for yourself… Let’s go, but I wouldn’t make it part of your infrastructure.   Not using Outlook, your pc blows up, or gets a Trojan and BAM. Bad Juju.

I still don’t know what you need to do… so it’s hard to give an informed answer, but if you’re exporting the data to SQL. I’m with Hal, either A. the data is in exchange leave it there, or B. the data shouldn’t be in exchange keep it in SQL. 

On the other hand, if you’re comfortable with it.. No a big deal.

GLENN SIZEMORE
INBOX
     STUFF
          MORE_Stuff

$outlook = New-Object -ComObject Outlook.Application
#attach to the mail store
$mailbox = $outlook.session.folders | ? { $_.Name -eq "GLENN SIZEMORE"}

#very long hand!
$inbox = $mailbox.Folders | ? { $_.name -eq "inbox" }
$stuff = $inbox.Folders | ? { $_.name –eq "STUFF"}
$more_stuff = $stuff.Folders | ? {$_.name –eq "MORE_Stuff"}

# lets say you wanted to read every message in "Stuff"
# Any message with SQL in the subject. Mark it as read and move it to more_stuff..

# subject should read:
# USER: glennsizemore schedualed for SQL maint.

#scriptblock... to find userID
$Finduser = {$_.substring($_.indexof("USER:") + 6,$_.indexof(" schedualed") -6)} 

$stuff.items | ? {$_.subject -match "SQL" } | % {
    #mark as read
    $_.UnRead = $FALSE
    
    #extract userid from subject
    $user = $_.subject | &$Finduser
    
    #move the message
    $_.move($more_stuff)
}

Do you have the SQL side already? You question was originaly about outlook.

~Glenn

I guess it does not have to be done on the desktop.

I have emails forwarded from various resources across the US into a generic mailbox(one point of data import). Some times up to 1000, two to three page word, pdf, html or text documents per day, and this number may grow to 5k per day.

The information is imported into a sql DB for ease of searching the content via full text indexing.

I know you can search & index via outlook or the windows search feature but it is too slow.

Once the data is in sql, the employees can access the sql DB and do there own searching.

The sql DB offers a more centralized solution.

I currently have about 1 million records in the DB and could growto 10 million quite easily in the next couple of years.

Speed of data import is important as is speed of data access.  

Automation of the whole process is very important!

Thanks for the feedback:)

I am not a DBA, not even close, but I know SQL can directly ingest that data. My suggestion would be to get Exchange out of the mix.  You sir need an application, not anything huge.  PHP or ASP would be my choice ingredient if your looking for homegrown, not Powershell.  You are exposing way too much power for a simple data ware house. 

It's one thing if SQL injection takes down the DB, but Powershell can turn it ALL OFF!

My 2c, focus on automating before it gets to exchange.  Perhaps look into a CMS, you have Windows Server standup SharePoint, or an open source variant like jumbia, or Mambo… Once the data hit’s exchange your already past automation.

~Glenn

Email is the easiest way for the data to get from mutliple remote locations, not always controlled by me, to a cental location that i do control. 

I can move it from email into same generic environment, but that involves one more step.

Oh the joy's of IT:)

I was told by a SQL Server DBA that SQL could import emails directly into the SQL SB.

He went awol, so i went to the SQL forums for advice, they sent me here, now i hear the SQL DBA reference again with data import directly into the DB:)       

It reminds me of a dog chasing its tail! 

Thanks for your help!

I will keep digging:)