String Concatenation in a DTS package.

Donovan · May 14th, 2004, 11:27 AM

Hello all,

I have been thrown to the wolves at my new job because the guy that was training me left abruptly. I am trying to figue out a DTS package and automate a task. A file gets placed on our ftp site once a week. The name of this file will change every week. The first part is achcon_ and the second part is fwxx with the xx being the fiscal week of the year, as in achcon_fw19 or achcon_fw20. I am trying to grab that file on our ftp site and move it to a working directory. Can't seem to get it to work. I have an e-mail after this code to notify me of a failure and that has been working.

I think my error is in concatenating the two strings.

Any help is apprieciated.

Code:

Function Main()

  Dim objFso 'File system object

  Set objFso = CreateObject("Scripting.FileSystemObject")
  DTSGlobalVariables("gv_strWklydir").value = "\\Database\WeeklyFiles\ACH\" ' Weekly directory
  DTSGlobalVariables("gv_strFTPDir").value = "\\Jax\D$\ClientFTP\GE\" ' FTP directory where ACH file gets uploaded
  DTSGlobalVariables("gv_strACHFile").value = "achcon_" & DTSGlobalVariables("gv_strFW").value & ".zip" ' ACH weekly file

  ' If the old update file exists, delete it.  This should never happen but just in case...
  If (objFso.FileExists(DTSGlobalVariables("gv_strWklydir").value & DTSGlobalVariables("gv_strACHFile").value)) Then
    objFso.GetFile(DTSGlobalVariables("gv_strWklydir").value & DTSGlobalVariables("gv_strACHFile").value).Delete
  End If

  ' Move the new ACH Group file to the weekly directory.
  If (objFso.FileExists(DTSGlobalVariables("gv_strFTPDir").value & DTSGlobalVariables("gv_strACHFile").value & DTSGlobalVariables("gv_strFW").value)) Then
    objFso.GetFile(DTSGlobalVariables("gv_strFTPDir").value & DTSGlobalVariables("gv_strACHFile").value).Move (DTSGlobalVariables("gv_strWklydir").value & DTSGlobalVariables("gv_strACHFile").value)
  Else      
    ' Email that the moving of the ACH file failed and exit the task,
    ' reporting a failure.
    strSubject = "GE Database - The moving of the ACH Group file failed."
    strMessage = "Hello DBAdmins," & vbCrLf & vbCrLf & "The moving of the ACH Group file " &_
                             DTSGlobalVariables("gv_strFW") & " failed.  Wassup?" & vbCrLf & vbCrLf & "Server"
    FrmtEmail strSubject, strMessage, strFileattachment
    Main = DTSTaskExecResult_Failure
    Exit Function
  End If

  Main = DTSTaskExecResult_Success

  Set objFso = Nothing

End Function

jemacc · June 14th, 2004, 03:42 PM

a few question.

1. Is the ftp site outside or side your fire wall?
2. Does account used to grab the file have access to the ftp site or folder your trying to access?

if number 1. then create a vbs script to move the file where you dts package can pick it up.
if number 2. Make sure the account has access to folders and FTP site.

Donovan · June 30th, 2004, 11:54 AM

It is inside our firewall, and I do have access to the ftp directory. I have placed a achcon.txt file in the ftp directory and was able to move that just fine. It is just the concatenation of these two ("gv_strACHFile")and ("gv_strFW") that throws the error.

abhishek.pandya · July 13th, 2004, 08:26 PM

Hi there

try to remove the .Value portion from the Gloabal variables.
I am not sure but you can try that.

e.g.

objFso.GetFile(DTSGlobalVariables("gv_strFTPDir") & DTSGlobalVariables("gv_strACHFile")).Move (DTSGlobalVariables("gv_strWklydir") & DTSGlobalVariables("gv_strACHFile"))

cheers
Abhi