SQL Server Performance Forum – Threads Archive

Ever changing named AS400 file to SQL Server

We have files on the AS400 that are named as atl0107, atl1206 and etc. We need to be able to retrieve the appropriate file to be ran in a stored procedure or DTS package. The problem is that when I attempt to retrieve the file in the following format, it states not found. ‘**********************************************************************
‘ Visual Basic ActiveX Script
‘************************************************************************
Function Main() DIM oConn Dim cn set oConn = CreateObject("ADODB.Connection") cn.Open "Provider=MSDASQL;Driver=SQL Server;server=(local);Trusted_Connection=Yes"
set rs.ActiveConnection = cn ‘Dim cn As New Connection
‘Dim rs As New Recordset ‘cn.CursorLocation = adUseClient
‘cn.Open "Provider=MSDASQL;Driver=SQL Server;server=(local);Trusted_Connection=Yes"
‘Set rs.ActiveConnection = cn
‘rs.Source = "Select * from sysobjects"
‘rs.Open , , adOpenKeyset, adLockOptimistic
‘rs.Close
‘
Set fso = CreateObject("Scripting.FileSystemObject")
strCurrentYear=DatePart("yyyy",Date)
strCurrentMonth=DatePart("m",Date)-1 strPreviousYear=CStr(DatePart("yyyy",Date)-1)
StrCMSFile=CSTR("CMSOHIO.S10FA72E.XJLUTZ.STKBATL")
if strCurrentMonth = 1 then
strCurrentyear2 = right(left(strPreviousyear,4),2)
else
STRCURRENTYEAR2=right(left(strCurrentyear,4),2)
end if
strfile2=strcmsfile&strcurrentmonth&strcurrentyear2
strfilea=cstr("CMSOHIO")
strfileb=cstr("S10FA72E")
STRFILEC=CSTR("XJLUTZ")
STRFILEP=CSTR(".")
STRFILED=CSTR("STKBATL")
MsgBox "The End Date is: " &strcurrentyear2
msgbox "file is: " &strcmsfile&strcurrentmonth&strcurrentyear2
msgbox "file being used is: " &STRFILEA&STRFILEP&STRFILEB&STRFILEP&STRFILEC&STRFILEP&STRFILED
‘ MsgBox "The End Date is: " & DTSGlobalVariables("EndDate").value
‘\As400MachineNameCMSOHIO.S10FA72E.XLUTZ.STKBATL fso.CopyFile "CMSOHIO"+"."+"S10FA72E.XJLUTZ.STKBAT"+StrCurrentMonth+StrCurrentyear,"C:STKBSTKBATL" rs.Close Main = DTSTaskExecResult_Success
End Function

---

So which bit is failing, and what do your debug messages show?<br /><br />All this code is irrelevant until the point where it tries to copy <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />

---

It is stating that the file CMSOHIO.S10FA72E.XJLUTZ.STKBAT1006 does not exitst. It does however exist.

---

What happens if you issue a COPY or XCOPY command on the Windows command prompt, spelling out the file names in full? Other points … Your month numbering apparently ranges from 0 to 11:
strCurrentMonth=DatePart("m",Date)-1 And your script is not always applying CStr() for the number-to-string conversion – although this is probably no big deal. Bigger problem could be that you might get leading blanks in the string, so you probably need to Trim() as well.
And check if the month format is perhaps 2-digit, like 01 for January, instead of just 1. It is also a little weird that the & concatenation operators in your script do not have a blank space before and after them, and that your script has both + and & for concatenation operators. Finally, it may help to concatenate the file paths before executing the fso.CopyFile command (use variables).

---

What about the source directory? At the moment you specify a destination path, but only a source filename The file may exist but does it exist in the location where CopyFile is looking? It would default to ‘current directory’ but its not always easy to predict what that might be

---

]]>