Poll For a File

Hi All,

I am using ActiveX code in a DTS package (Package1) to search a directory for a file.
When the file is there I want to start a DTS package (Package2) to load
in this file.

I will schedule Package1 to run at 10pm, however since the external file is being generated externally I cannot guarentee that the file will be there at 10pm. Therefore I want to Poll the folder and not start package2 until I have the file. I am using the If file exists method.

How do I poll for a file, and if its not there wait for 5 mins and poll again until I find the file.

Thanks in advanceHave you tried FSO objects?

Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")|||I have tried the FSO and thats what I am using.

My problem is that if the file is not there, I want to wait for a period of
time ie 5 mins and then try again. And keep trying every five mins until I get the file.

Some thing like this:

========================
Function Main()

dim result

set objFSO = CreateObject("Scripting.FileSystemObject")

result = 1

DO WHILE result = 1
If objFSO.FileExists("c:\test.txt") Then
//Start other Package
result = 99
Else
wait(5)
//Go to start and check for file again
End if

LOOP

End Function
=========================

So in essence the above code will LOOP until it finds the file.
I just need something to replace the wait(5)

Any Ideas

Thanks in advance

I have wait(5) but wait is not a valid function|||EXEC [master].[dbo].[xp_fileexist] 'c:\new_auth.dat1'

This might help|||Originally posted by superquinn
I have tried the FSO and thats what I am using.

My problem is that if the file is not there, I want to wait for a period of
time ie 5 mins and then try again. And keep trying every five mins until I get the file.

Some thing like this:

========================
Function Main()

dim result

set objFSO = CreateObject("Scripting.FileSystemObject")

result = 1

DO WHILE result = 1
If objFSO.FileExists("c:\test.txt") Then
//Start other Package
result = 99
Else
wait(5)
//Go to start and check for file again
End if

LOOP

End Function
=========================

So in essence the above code will LOOP until it finds the file.
I just need something to replace the wait(5)

Any Ideas

Thanks in advance

I have wait(5) but wait is not a valid function

Why do not create a new function (I did not check it, sorry) and call it:

Function wait(theDate)
Do Until DateDiff("n", Now, theDate)<5
Loop
End Function|||I dont want to write a loop function like you have suggested as this constant looping might be very hard on the CPU. I was hoping there was a clever way or else an in built wait/sleep function available to me in the ActiveX component of the DTS.

Can Anyone help?|||You could write a Agent Job that will run every 5 minutes from 10 to 12. Sounds like the easiest way.|||Hi,

Im not quiet sure I understand what you mean by an agent Job.

I am only new to SQL Server and DTS.

The polling for a file is causing a lot of trouble for me.
Can someone please help|||In enterprise Manager, expand Management, SQL Server Agent, then click on Jobs. From there you can add a new job, which can execute ActiveX scripts, T-SQL scripts, even DTS packages. You can add one or many schedules to kick off the job.

I think it may be what you are looking for.

You can get started by right-clicking on the DTS package you created, and selecting Schedule Package, which will set the DTS package up to run as a job. You can then adjust the schedule to your liking, add more steps, etc.|||Would the command WAITFOR DELAY be useful?

Here is the help from MS SQL Server Books Online...
WAITFOR
Specifies a time, time interval, or event that triggers the execution of a statement block, stored procedure, or transaction.

Syntax
WAITFOR { DELAY 'time' | TIME 'time' }

Arguments
DELAY

Instructs Microsoft SQL Server to wait until the specified amount of time has passed, up to a maximum of 24 hours.

'time'

Is the amount of time to wait. time can be specified in one of the acceptable formats for datetime data, or it can be specified as a local variable. Dates cannot be specified; therefore, the date portion of the datetime value is not allowed.

TIME

Instructs SQL Server to wait until the specified time.

Remarks
After executing the WAITFOR statement, you cannot use your connection to SQL Server until the time or event that you specified occurs.

To see the active and waiting processes, use sp_who.

Examples
A. Use WAITFOR TIME
This example executes the stored procedure update_all_stats at 10:20 P.M.

BEGIN
WAITFOR TIME '22:20'
EXECUTE update_all_stats
END

For more information about using this procedure to update all statistics for a database, see the examples in UPDATE STATISTICS.

B. Use WAITFOR DELAY
This example shows how a local variable can be used with the WAITFOR DELAY option. A stored procedure is created to wait for a variable amount of time and then returns information to the user as to the number of hours, minutes, and seconds that have elapsed.

CREATE PROCEDURE time_delay @.@.DELAYLENGTH char(9)
AS
DECLARE @.@.RETURNINFO varchar(255)
BEGIN
WAITFOR DELAY @.@.DELAYLENGTH
SELECT @.@.RETURNINFO = 'A total time of ' +
SUBSTRING(@.@.DELAYLENGTH, 1, 3) +
' hours, ' +
SUBSTRING(@.@.DELAYLENGTH, 5, 2) +
' minutes, and ' +
SUBSTRING(@.@.DELAYLENGTH, 8, 2) +
' seconds, ' +
'has elapsed! Your time is up.'
PRINT @.@.RETURNINFO
END
GO
-- This next statement executes the time_delay procedure.
EXEC time_delay '000:00:10'
GO

Here is the result set:

A total time of 000 hours, 00 minutes, and 10 seconds, has elapsed! Your time is up.|||Hi,

Thanks for your reply. I dont think I am explaining myself fully

I know how to sechdule the Package.
I know how to check if file exists

What I need to know is how to halt processing in a activex script
for a period of time.

If the file is not there initially, I want the wait a while and the reloop to
the start of the package and so on until the file is there

==========ACTIVEX SCRIPT========

Set foundfile = 0

Do While foundfile = 0

if foundfile = 1 then
set foundfile = 1 (This breaks loop)
//Carry on processing
Set Success
else
//Halt procesing for 5 mins and then continue
//This is where I need HELP!!

end if

loop|||I think we understand what you are asking, but are offering solutions that differ from the model you are chasing.

For instance, when I mentioned a scheduled job, I meant that you could create a task that would check for the file's existence. If were not there, the job would schedule itself to run again in a few minutes, then exit. If it were there, proceed to the next step, which would kick off your DTS package.|||The problem with having an ActiveX script wait, is that you are tying up a thread and processor resources just counting away clock cycles. The SQL Agent dooes that already, so you might as well take advantage of it.|||Originally posted by bpdWork
I think we understand what you are asking, but are offering solutions that differ from the model you are chasing.

For instance, when I mentioned a scheduled job, I meant that you could create a task that would check for the file's existence. If were not there, the job would schedule itself to run again in a few minutes, then exit. If it were there, proceed to the next step, which would kick off your DTS package.

=====================================
This sounds EXACTLY like what I want to do. Im sorry about this
but Im new to SQL Server.

How do you get a Job to reschedule itself in code?
I assume this is done in ActiveX|||Off the top of my head, I would say that you would need to use an ActiveX script to access the SQLDMO object, and control it from there. I have done such a thing before. I will see what I can find in my old code heap.|||I think I can help

You should create your package wiht the activex script ect...
Then schedule it to run every 5 mins. as a SQL job.

The active x script should look like this. No need to make the activex script wait, just have it run again and again. If no file, it just quits, when it finds one it runs the other import package.

Function Main()

Dim objFSO
Dim objFolder
Dim objFile
Dim oPKG
Dim fileName
Dim folderName

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("\\server\share$\dir\")

For Each objFile in objFolder.Files

fileName = objFile.Path

Set oPKG = CreateObject("DTS.Package")
oPKG.LoadFromSQLServer "Server", "sa", "", , , , , "2nd package name here"
oPKG.GlobalVariables("fileName").Value = fileName
oPKG.Execute
oPKG.Uninitialize()
Set oPKG = Nothing

objFSO.MoveFile fileName, folderName 'move file somewhere
Next

Main = DTSTaskExecResult_Success

End Function

Hope this helps

Steve|||yes thats the easiest way,

The first step in your package will be a activeX task with the following script inside

==========================================
Function Main()
Dim fs, MyFile
Dim FileLocation
Dim FileName
Dim FileInfo

FileName = "file.txt"

FileLocation = DTSGlobalVariables("Invoice_Input_Location").Value

FileInfo = FileLocation & FileName

Set fs = CreateObject("Scripting.FileSystemObject")

If (fs.FileExists(FileInfo)) Then
fs.MoveFile (FileInfo),(FileLocation & "new_file.txt")
set fs=nothing
Main = DTSTaskExecResult_Success
exit function
Else
Main = DTSTaskExecResult_Failure
exit function
End If

End Function
==========================================

rest of the steps inside the DTS will be after success of this task. The package will be schedules to run after every 5 mins, if your file is present, it will be renamed with a different name (so that next time when the package runs afetr 5 mins it does not process it all over again), this renamed file can be used for further transformations. This way the package will run successfully just once everyday and will fail for rest of the times. which is what you want.

Plz help me! I want to use SQL Server2000 DTS Import/Export Wi

I'd still suggest using either a bulk insert or bcp. After your last
explanation, maybe bcp would be better. It'll definitely be faster than
inserting a row at a time. First of all, you can do massive inserts without
logging which will reduce overhead and speed the process. Second, you can
write your C# code to change the bcp command line, for example, change the
table name or the test file path/name according to whatever parameters you
want. In your program, write the bcp command to a windows .bat file then
execute a shell to run the bat if that makes more sense. That way you have
the flexibility to change the insert parameters on the fly and you get the
speed of bcp inserts.
Other than performing an import from text to sql table, what do you mean by
this statement?
"i want to get the functionality of DTS from C#. i mean i want
to use SQL Server objects and libraries to perform SQL server related
tasks from my application."
"Adnan" wrote:

> thanks for sparing time for me.
> actually i have log files that are very huge sized like 700MB or even
> sometimes these reach upto GBs.
> i want to extract information from these files u can say i want to
> apply filters to get only required rows. log files are maintained by
> IIS and contain space seperated fields in each row and there are
> millions of rows.
> now currently i m reading file line by line and inserting that line
> into a database table. after that i select data from that table using
> different filtering criteria and then binding that selected data to a
> data grid.
> now the problem is when i use huge sized files the process becomes very
> slow and user hav to wait for a long time so that the file to be read
> line by line and get inserted into database line by line.
> there are different sample codes that are related to execute DTS
> packages. i want to get the functionality of DTS from C#. i mean i want
> to use SQL Server objects and libraries to perform SQL server related
> tasks from my application.
> i hope that now u hav understood the scenario. the other thing is if i
> create a DTS package from sql server and while executing it i want to
> change its attributes like file name at run time so that i give file
> name as a parameter and the package with that file name get executed.
> if u still hav confusions abt understanding scenario then plz do tell
> me.
> thanks.
> best regards.
>BCP is very much like "bulk copy" technically, but BCP is a dos command. Yo
u
can read all about it in the Sql Server Books Online.
What I'm suggesting is that you can write your C# code to dynamically create
or edit a BCP command in a .bat file (technically it's just a text file).
You can manipulate the file/folder name or even the sql server table name
that the BCP will load if that makes sense in your application. You can set
it to load a table from a formatted or delimited file. For example, you sai
d
you have a space separated file.
bcp is pretty easy to set up and runs quickly.
A simple example of a bcp command would look like this:
bcp "database1.dbo.tbl_target_table" in "c:\foldername\file1.txt"
-c /S"servername" /U"userid" /P"userpassword" -f"c:\foldername\formatfile.fm
t"
In this example, the process is to load an existing sql table
(tbl_target_table) from a text file (file1.txt) using a format file that you
pre-created (formatfile.fmt)
There are many options for BCP depending on your needs. You would be
interested in the -t (field terminator character) and the -r (row terminator
character) which is where you'd tell it that a space is a field terminator
and a Cr/LF is the row terminator. As long as you can run a dos command fro
m
within C#, you can do this. BCP is part of Sql Server, not .Net per se, so
I
don't think you'll have any problems with your version.
"Adnan" wrote:

> hy
> thanks again :-)
> r u talking about SqlBulkCopy? is it BCP? as far as i know about
> it(SqlBulkCopy), it is supported in version 2 of Dot net and in my
> company we r using version 1.1. so if BCP is SqlBulkCopy then it is not
> possible now. and if this is other thing then i m going to check it and
> plz i'll b very thankful to u if u provide me related web links or
> material along with ur valuable suggestions.
> as far as my statement is concerned actually i was going to use SQLDMO
> and finding solution via SQLDMO. at present i hav to only import txt
> files but in future may b i need it.
> i m really thankful to u that u spared ur time for me because i have
> posted this problem at different forums but u r the only ho replied.
> my email id is "adnan.developer@.gmail.com" if u like to attach some
> useful material.
> best regards.
>|||Adnan,
Sorry, I can't help you with C# code. I don't use it. But I've done
similar processes from VB. I suggest you try a different MS community for C
#
programming help. Ask about opening a Dos command shell from c#. I'm sure
you can do this, and it's probably not very difficult.
Microsoft's sites will mostly just describe the command and options, as you
found. They will not help much if you are looking for specific code. But,
there are some good IT community sites other than Microsoft that offer tips
and code for just about any languange. You can search for them on the web
probably find something useful quickly. Use your favorite search engine and
find "C# programming examples" Here's one I like to use: www.ittoolbox.com
You need to know two main programming concepts: 1 how to create/update a
text file from within c#. 2: how to open a dos command shell and execute a
.bat or .cmd command file.
To use it, your application must know how to set up the BCP. that is, your
app must know the pathname/file name of the the text data file an of the BCP
.bat file. That's up to you to determine. Next, you would need to write a
BCP command to the .bat file using the the path/file name of the data, and
the SQL server, database, table, etc. that will be loaded. Last, you need t
o
open a dos command shell, and tell it to execute the BCP .bat file. All of
this can be done in the application without the user intervention if that's
what you need to do.
Good luck.
"Adnan" wrote:

> hello
> how r u tthrone?
> i hav read ur suggestion and i think it is v close to my solution but
> one thing that i hav to telll u that i m not an expert of .net i m new
> to it.
> as i found abt BCp it is a command line utility as u also told. now the
> problem is that from where i can execute it? i mean i m going to make a
> GUI windows based application and i m confudsed that how to implement
> it in my windows based application. plz help me regarding:
> if BCP is a command line utility then fromn which command prompt i can
> execute it? .net command prompt or which.
> and how and where i hav to use it in my application. i dont want my
> user to go to any command prompt.
> actually as it looks i m not clear about BCP that how it works and how
> it is used.
> i'll b very very thankful to u if u help me in this regard because i
> have already taken very much time from company for this.
> plz if it is possible then do provide me some sort of code or at least
> try to clear the concept that what steps should b taken to use BCP from
> my C# application.
> or if u know the link of such a web page then plz tell me because i hav
> searched my best but couldnt find satisfactory help. i hav already
> searched MSDN library it only tell about BCP but doesnt tell that how
> to use it in my application. it just tells its syntax and parameters
> details.
> best regards.
> Adnan Akram.
>