New to DTS, help needed with creating a package!
-
Hi all,
I was wondering if it were possible to automate the following processes:
Have a DTS package unzip a winzipped csv file in my Outlook inbox, import the contents to a new table according to the existing layout of the csv (rather like the 'Select Into' command in SQL Server), and every subsequent day append the contents of a new, zipped csv (which will always have the same layout) into the database.
I suspect it'd be simpler to manually import the first file, along with the in-process creation of the table, in advance of the first running of the package. The biggest problem I foresee is getting the package inside Outlook, which would keep the csv file in a .dbx file, I believe.
I'd be grateful for some tips, and perhaps an overview of how I should go about this.
Thanks in advance,
KS
-
There might be a few ways to structure this. Off the cuff, it seems that you might do well to do some Outlook Automation inside of Outlook first (using VBA for Outlook). You could monitor incoming email messages and examine their subject, attachments, etc. If the message subject or attachment name matched a specific string, your code could save the zipped file somewhere (say, a specific folder on a network share).
Then your code would use a shell command to call Winzip and have it unzip the file.
You'd probably want to save it with a unique file name based on the date (for example, mygroovyfile_20040418.csv).
Then, you could do one of a few things. You could invoke a DTS package using SQL Server automation (calling it from within Outlook VBA). Or, you could have a DTS package scheduled in SQL Server to check the network share each day, and load this csv package if it found it. If it didn't find it, it could email you (or whomever) a message saying that the groovy file wasn't there.
A simpler alternative would be to have the file uploaded to an ftp site each day. There are code samples on SQLServerCentral.com for checking an ftp site for a specific file and pulling it down if it is there. You save the whole tedious Outlook part of the process.
Sounds like an interesting challenge. Best of luck!
-
Thanks Andy, the ideas are appreciated, although I'd hoped for a click-by-click explanation, I'm grateful for any assistance.
Also, I've not used VB or shell commands before, so the venture is definitely challenging!
Thumbs up,
KS.
-
Here's the Outlook part. Put this in the ThisOutlookSession class (open Outlook, hit Alt-F11, hit Ctrl-R, expand Microsoft Outlook Objects, open ThisOutlookSession).
This reviews your inbox every time a message is added to it. If the message has a subject of "Usual Suspects" it takes the attached file and moves it to a folder called c:\foobar.
You will need to get your hands on a command-line DOS utility which unzips files (just Google "DOS zip utility"). Once that's installed, and you have worked out the syntax for unzipping the file, we can do the next 2 steps (DTS step to call the unzip utility, and DTS step to load data to table).
==============================================================
Public Sub Application_NewMail()
Dim ns As NameSpace
Dim Inbox As MAPIFolder
Dim Item As Object
Dim Atmt As Attachment
Dim FileName As String
Dim sFilePath As String
sFilePath = "c:\foobar\" 'the folder where you want the file to wind up
Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
Set Item = Inbox.Items(1)
'check that it's the email we are looking for....
If Item.Subject = "Usual Suspects" Then
'quit if there are not exactly 1 attachments
If Item.Attachments.Count 1 Then
MsgBox "An email came in with this many attachments: " & Item.Attachments.Count
Exit Sub
End If
'now save the only attachment out to a folder
Item.Attachments(1).SaveAsFile sFilePath & Format(Now(), "yyyymmdd") & ".zip"
End If
End Sub
-
Hi Andy,
If you're still up for doing this, I could really use your guidance!
Either way, thank you.
KS.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply