April 2, 2008 at 7:57 am
Hi..I have a FTP Location where every hour a new text file(comma separated values) is uploaded from an external source. I need to create a DTS Package which will check the FTP Location everyhour and if a new file is found it should retrieve the same and then dump the contents in a table of Oracle which resides on another server.
Urgent help pls.
Thanks
April 2, 2008 at 8:31 am
help needed urgent....
April 2, 2008 at 8:39 am
Hi,
Please be more specific about what you need help with. It would be nearly impossible for anyone to give you details about everything you need to do to get this to work with DTS.
The first question I'd ask is, can you import the data directly into Oracle using Oracle utilities? When I worked with Oracle, there was a utility called SQL*Loader that loaded data from external files.
Greg
April 2, 2008 at 8:52 am
hi..I just need to retrieve the new coming text files from a FTP Location and transfer to a table of oracle on my Computer
May 19, 2008 at 4:20 pm
hi
Did you get the answer for this question?.
I am also facing the same problem,but my problem is that I need to get an xml file from FTP server and after that in DTS(SQLServer2000)package,I need to pass this xml file as a parameter to storedprocedure,in storedprocedure I need to parse this xml file and take the all nodes values and needs to store in a table.
My problem is that I am not able to connect to FTP server to grab the xml file,I dont know how to connect to the file stored in FTP server through VB Activex script in DTS package, do we have any connection string to connect to specific FTP server in SQLServer2000 DTS package like as ADODB connection[/color]
Plz let me know frens..give me any ideas.
Thanks
May 19, 2008 at 4:37 pm
The first question I'd ask is, can you import the data directly into Oracle using Oracle utilities? When I worked with Oracle, there was a utility called SQL*Loader that loaded data from external files.
Well, what about the oracle utilities?
As far as using DTS, this is an extremely simple task. What have you tried that has you stuck?
May 19, 2008 at 4:39 pm
pvnlaks.mscis,
What makes your problem similar? Are you having problems with the FTP part or the XML part? I would suggest starting yourself a new thread with a specific question about what you are having a problem with. Since you're a new poster, here's a good read on how to get some good help here on SSC:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 20, 2008 at 3:46 am
Nitin,
Follow the following steps:
1) Firstly create a new DTS package
2) Drag an Execute SQL Task with the following code to check if the file is present or not:
SET @Cmd = 'DIR \\ \' + @FullFileName
EXEC @Result = master.dbo.xp_cmdshell @Cmd, no_output
IF @@ERROR <> 0-- xp_cmdshell produced an error.
BEGIN
RAISERROR (60104, 16, 1, @FullFileName, @ProcName) WITH LOG SET @Success = -5
GOTO TheEnd
END
IF @Result <> 0-- The file does not exist.
BEGIN
RAISERROR (60103, 16, 1, @FullFileName, @ProcName) WITH LOG
SET @Success = -6
GOTO TheEnd
END
3)On success of this task navigate to a text file source connected to the source file which is connected to an ODBC driver for Orcale using Transform Data Task
4)On failure of the Execute SQL Task you can do what ever error handling you require like notifying you through email or do nothing or updating a table etc..
REgs,
Samata
May 20, 2008 at 4:07 am
An added functionality:
You can archive and delete the file after it has been copied over to the oracle table.
----------------------------------------------------------------------------------------------
-- Copy the file to the archive folder.
----------------------------------------------------------------------------------------------
SET @Cmd = 'COPY \\servername\path\' + @FullFileName
+ '\\servername\path\' + @ArchiveFolder
EXEC @Result = master.dbo.xp_cmdshell @Cmd, no_output
SET @ErrorNo = @@ERROR
IF @ErrorNo <> 0 OR @Result <> 0
BEGIN
RAISERROR (60102, 16, 1, @FullFileName, @ProcName) WITH LOG
SET @Success = -11
GOTO TheEnd
END
----------------------------------------------------------------------------------------------
-- Delete the import file.
----------------------------------------------------------------------------------------------
SET @Cmd = 'DEL \\servername\path\' + @FullFileName
EXEC @Result = master.dbo.xp_cmdshell @Cmd, no_output
SET @ErrorNo = @@ERROR
IF @ErrorNo <> 0 OR @Result <> 0
BEGIN
RAISERROR (60109,16,1, @FullFileName, @ProcName) WITH LOG
SET @Success = -12
GOTO TheEnd
END
May 20, 2008 at 7:38 am
hi..this is the EXACT thing what i was looking for but all this have to be done on an FTP location not a shared server. Please help me on that. Thanks for ur effort
May 20, 2008 at 7:49 am
Could you clarify on what do you mean by everything done on FTP site and not on shared server???
The above posted code works out everything on the FTP site but uses DTS as a tool to do the transformation to Oracle.
You can use command line arguments in DOS to do the same.
May 20, 2008 at 8:48 am
May 20, 2008 at 9:53 am
Hi Samata,
My code is working now i made it like this...thanks for ur effort..
Function Main()
On Error Resume Next
Dim strDestPath, fNewFile, strSourcePath, fOldFile, sBackup
Dim fso, f, f1, fc, fcreated, fname, fldrItem, fldrName,x
Dim objTxtFile, baseDate
' Initialize the variables
strSourcePath = "PATH"
Set fso = CreateObject("Scripting.FileSystemObject")
' Create the Text File
Set objTxtFile = fso.CreateTextFile( strSourcePath & "\ftptest.txt", TRUE)
' Write the Header
objTxtFile.writeline( "open FTPSERVER" )
objTxtFile.writeline( "USERNAME" )
objTxtFile.writeline( "PSWD" )
objTxtFile.writeline( "cd FOLDER" )
Set f = fso.GetFolder(strSourcePath)
For Each fldrItem in f.SubFolders
Set fc = fldrItem.Files
fldrName = fldrItem.name
fname = " "
For Each f1 in fc
objTxtFile.writeline( "put """ & strSourcePath & "\" & fldrname & "\" & f1.name & """")
Next
Next
objTxtFile.writeline( "quit" )
objTxtFile.Close
Set objTxtFile = Nothing
Set fso = Nothing
Main = DTSTaskExecResult_Success
End Function
and then called this .txt file in a batch file like this --
and then i created a batch file where i wrote this statement
ftp -s:"\\SERVERNAME\ftptest.txt"
and this batch file was called in a stored procedure
Rgds
Nitin
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply