May 30, 2008 at 9:16 am
I have 3 folders in a FTP Location..(X1,X2,X3),
I want to create a DTS package which will go to these folders daily and if a new files exists then I need to pick that file and process those details in another DTS Package once finished again should loop the same for another file if any in the same folder, then it shuld go to the next folder and do the same till all the files are processed in all folders.
CAN SOME BODY PLEASE HELP ME ITS URGENT
Thanks in advance
May 30, 2008 at 9:34 am
Is this DTS or SSIS? You've now got the question posted in two forums under two different versions. Urgent perhaps, but you're not going to get coherent help if you start us down the wrong path.
In DTS - you're likely going to be contrained to an ActiveX task to get this done. Alternatively - write the DTS package to handle a single file, with a global variable to pick the name of the file, and do the name looping outside of SQL Server (calling DTSRUN for example....)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 30, 2008 at 9:54 am
I am using DTS..it was urgent thats why posted at multiple location my apologies..It would be nice if you can link me to some samples..as i am able to loop on the Local server but i am not able to do in FTP SERVER..
May 30, 2008 at 10:03 am
Do you need to do the intial processing using DTS?
you could create a batch file that goes into each directory and copies out the latest file to a path and give it a common name for your second DTS package to process.
May 30, 2008 at 10:16 am
I need the initial processing itself, where in the file names from different folders get copied to a Global Variable which then i can pass that to my other package.
can you please tell me how this batch file can be created as i am not able to get the name of the files from different foders..I am using this script to get inside the http://ftp..after this what to do i am not getting..pls help
Function Main()
Dim ftpServerName,ftpPath,ftpUserName,ftpPassword
ftpServerName=CStr(DTSGlobalVariables("ftpServerName").Value)
ftpPath = CStr(DTSGlobalVariables("ftpPath").Value)
ftpUserName = CStr(DTSGlobalVariables("ftpUserName").Value)
ftpPassword= CStr(DTSGlobalVariables("ftpPassword").Value)
Dim strFTPScriptFileName
Dim objMyFile
Dim objFSO
strFTPScriptFileName = "c:\log.txt"
set objFSO = CreateObject("Scripting.FileSystemObject")
If (objFSO.FileExists(strFTPScriptFileName)) Then
objFSO.DeleteFile(strFTPScriptFileName)
End If
set objMyFile = objFSO.CreateTextFile(strFTPScriptFileName, True)
objMyFile.WriteLine("open " & ftpServerName)
objMyFile.WriteLine(ftpUserName)
objMyFile.WriteLine(ftpPassword)
objMyFile.WriteLine("cd " & ftpPath)
objMyFile.WriteLine("bye")
objMyFile.WriteLine("pause")
objMyFile.Close()
set objFSO = Nothing
set objMyFile = Nothing
'The following code executes the FTP script. It creates a Shell
'object and run FTP program on top of it.
Dim objShell
set objShell = CreateObject("WScript.Shell")
objShell.Run("ftp -s:" & Chr(34) & strFTPScriptFileName & Chr(34))
set objShell = Nothing
Main = DTSTaskExecResult_Success
End Function
May 30, 2008 at 10:57 am
Sorry i did not fully appreciate the FTP part of the problem, if you had direct acces to the file paths you could build up a batch file to copy the latest file out of there.
something like this has worked for me for file paths
@echo off
setlocal
set source=c:set destdir=c:\
pushd "%source%"
for /f "tokens=*" %%a in ('dir autoexec.* /b /a-d /o:e 2^>NUL') do (set lfile=%%a)
echo copying "%source%\%lfile%" to "%destdir%" As Filename.txt
copy /y "%source%\%lfile%" "%destdir%\Filename.txt
May 30, 2008 at 11:06 am
i really appreciate ur help..but this will not work for FTP Locations...and i dont have direct access to the FTP path..any other suggestions...or examples..
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply