PickUp .txt then DropTo .xls using DTS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

PickUp .txt then DropTo .xls using DTS

Author

Topic

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2008-08-18 : 13:47:07

I'm not exactly new to S2K, but I am new to DTS packages. I'm trying to pick up a .txt file from a FTP site and drop it to my file server in a .xls format. I searched this site and didn't see any posts that address this. I created a .txt source but am having a hard time creating the .xls destination.

Another problem I have is that the file name will change every week due to the date being in the file name.

I have a VBScript that I've copied that picks up and drops .xls files in the manner I need it done. However, I'm not familiar enough with all of this to know what to do or how to do it.

Any (detailed) assistance would be greatly appreciated.

****Code I use for straight pick and drop****

Const strSourceFolderName = "D:\BP_FILES\Customer_Profile"
Const strDesFolderName = "\\<file path>\Customer_Profile"
Const strLocalFolderName = "D:\BP_FILES\Customer_Profile"

'***********************************************************************
' FTP Server Constant
'***********************************************************************
Const strFTPServerName = "<ftpsite>"
Const strLoginID = "<login>"
Const strPassword = "<password>"
Const strFTPServerFolder = "outbound"
Dim strFileName, strDateFormat

Function Main()
Dim mFile

'--- Get File Name
strDateFormat = DatePart("yyyy", Date) & IIf(Len(Month(Date)) < 2, "0" & Month(Date), Month(Date)) & IIf(Len(Day(Date)) < 2, "0" & Day(Date), Day(Date))
strFileName = "cust_prof_" & strDateFormat & ".txt"

'--- USE FOR MANUAL LOAD
strFileName = "cust_prof_20080818.txt"

If 1 <> 1 Then '--- Use for testing
Exit Function
End If

'**** Delete Local File If Exist ****************************
mFile = strSourceFolderName & "\" & strFileName
Set objFSO = CreateObject("Scripting.FileSystemObject")
If (objFSO.FileExists(mFile)) Then
objFSO.DeleteFile (mFile)
End If
'**** Delete Local File If Exist ****************************

Call FTP_File()

'**** mFile = strSourceFolderName & "\" & strFileName
'Set objFileCopy = objFSO.GetFile(mFile)

'strDestination = strDesFolderName & "\" & strFileName
' Copy the file to its destination
'objFileCopy.copy (strDestination)

'***** Clean-up ******************
Set objFSO = Nothing
'Set objFileCopy = Nothing
Main = DTSTaskExecResult_Success

End Function

Function FTP_File()

Dim objFSO, objMyFile, objShell, strFTPScriptFileName, strFile2Get

strFile2Get = strFileName

'The following lines of code generate the FTP script file on the fly,
'because the get file name changes every day

strFTPScriptFileName = strLocalFolderName & "\FTP\" & "FTPScript.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")

If (objFSO.FileExists(strFTPScriptFileName)) Then
objFSO.DeleteFile (strFTPScriptFileName)
End If

'--- Generate FTP file.
Set objMyFile = objFSO.CreateTextFile(strFTPScriptFileName, True)

objMyFile.WriteLine ("open " & strFTPServerName)
objMyFile.WriteLine (strLoginID)
objMyFile.WriteLine (strPassword)
objMyFile.WriteLine ("cd " & strFTPServerFolder)
objMyFile.WriteLine ("ascii")
objMyFile.WriteLine ("lcd " & strLocalFolderName)
objMyFile.WriteLine ("get " & strFile2Get)
objMyFile.WriteLine ("bye")
objMyFile.Close

'The following code executes the FTP script. It creates a Shell
'object and run FTP program on top of it.
Set objShell = CreateObject("WScript.Shell")
Set WSX = objShell.Exec("ftp -s:" & Chr(34) & strFTPScriptFileName & Chr(34))
Set ReturnCode = WSX.StdErr
Set Output = WSX.stdOut

strErrorLog = strLocalFolderName & "\FTP\Log\" & "ftpErrors_" & strDateFormat & ".txt"
Set objMyFile = objFSO.CreateTextFile(strErrorLog, True)
objMyFile.Write (ReturnCode.ReadAll())
objMyFile.Close

strLog = strLocalFolderName & "\FTP\Log\" & "ftpLog_" & strDateFormat & ".txt"
Set objMyFile = objFSO.CreateTextFile(strLog, True)
objMyFile.Write (Output.ReadAll())
objMyFile.Close

Set objFSO = Nothing
Set objMyFile = Nothing
Set objShell = Nothing
Set WSX = Nothing
Set ReturnCode = Nothing
Set Output = Nothing

End Function

Function IIf(expr, truepart, falsepart)
IIf = falsepart
If expr Then IIf = truepart
End Function

'************************************************************************
' Error Handling
'************************************************************************
Function fntErrorHandling(oErr)

fntErrorHandling = False

If oErr.Number Then
fntErrorHandling = True
Dim strTo, strFrom, strSubject, strBody
strTo = "<email address>"
strFrom = "Negative.File@sprint.com"
strSubject = "Negative.File Package Error."
strBody = oErr.Description
Call SendSMTPMail(strTo, strFrom, strSubject, strBody, "")
End If

End Function

'************************************************************************
' Email Notification
'************************************************************************
Function SendSMTPMail(mTo, mFrom, mSubject, mBody, mFile)

Dim objMail

'---Initialize Object
Set objMail = CreateObject("CDONTS.NewMail")

'---Create e-mail and Send
With objMail
'----Encode it as MIME format NOT text. Otherwise it arrives as all text in some mail readers.
.BodyFormat = 1 ''Set the e-mail body format (0=HTML 1=Text)
.MailFormat = 1 ''Set the mail format (0=MIME 1=Text)

.From = mFrom
.to = mTo
.Cc = "<email address>"
.Subject = mSubject
.Body = mBody

If Len(mFile) > 0 Then
.AttachFile mFile
End If

.Send

End With

Set objMail = Nothing

End Function

Function fntWait(seconds)
startTime = Time() ' gets the current time
endTime = DateAdd("s", seconds, startTime)
While endTime > Time()
'DoEvents
Wend
End Function

Thank you in advance for your assistance.
Peg.

*************************
Got some code from Rob. Can anyone help?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources