Import a file in a directory

Question

Post reply

Import a file in a directory

Saurabh Mathur

Mr or Mrs. 500

Points: 591
More actions
December 28, 2005 at 7:43 pm

#68058

I had to create a DTS job that would grab a file from a particular directory, whenever a new file is added, and then import it using some procedure that i had created. To get the file, i try to grab it, and store in a temp table on the database. Using help from http://www.databasejournal.com/features/mssql/article.php/1461661
i modified my activex script to look like this:
Function Main()
' Declare FSO Related Variables
Dim sFolder
Dim fso
Dim fsoFolder
Dim fsoFile
Dim sFileName
' Import Folder
sFolder = "D:\Evaluate_CCExtractFileJulWithWOff2\"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(sFolder)
For Each fsoFile in fsoFolder.Files
' Get first filenme
if fsoFile.Name <> "Dummy.txt" then
sFileName = sFolder & fsoFile.Name
Exit For
end if
Next
'Declare Variables
Dim oPKG
Dim oConnection
' Get Package Object
Set oPKG = DTSGlobalVariables.Parent
' Get Source Connection Object
Set oConnection = oPKG.Connections("Text File (Source)")
' Set new Filename
oConnection.DataSource = sFileName
DTSDestination("Temp_Record") = DTSSource("Col001")
Main = DTSTransformStat_OK
End Function
The dummy.txt is a file i made, which is pointed to by the Text File (Source) connection. Now, i specifically said in the code that if its dummy.txt, then loop and get the new file, but always that i run it, it just gets the dummy.txt for no reason.
Can you tell me where im going wrong?
Thanks a ton

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 1

Try adding message boxes to the code to try to debug it, like...

For Each fsoFile in fsoFolder.Files

if fsoFile.Name <> "Dummy.txt" then

sFileName = sFolder & fsoFile.Name

MsgBox sFileName & " " & fsoFile.Name

Exit For

end if

MsgBox sFileName & " " & fsoFile.Name

Shane Hall Valued Member Points: 64 More actions · Answer 2

Might be case sensitivity. Try;

if lcase(fsoFile.Name) <> "dummy.txt" then

Regards

Shane

Saurabh Mathur Mr or Mrs. 500 Points: 591 More actions · Answer 3

Thanks for that guys.

It was infact that i had declared the activex script after the text connection was established.

I changed it so that i do the activex stuff first, and then do a "workflow-success" arrow to the text connection and it does fine now.

Thanks for the help anyway.

Import a file in a directory

Cookies on SQLServerCentral