mujoshi
asked on
Is it possible to execute a DTS package as a particular event occurs?
Hi,
I'm working on the following task
One of the current application exports a file in the text file format to a specified directory. As soon as the file arrives in a specified directory the process should start which will import data from a text file to a SQL server table and then move that text file to a 'processed' directory. After the import part is completed successfully a report is generated in the text file format in a particular directory and then the report is mailed to muliple users.
I'm thinking of a DTS package for this functionality. But then I don't want to schedule the DTS package but the package should be executed only when a new file arrives in the specified directory.
Is it possible to execute a DTS package as a particular event occurs? Or is there another way to do this like using VBScript calling DTSRun etc?
Thanks for the help.
I'm working on the following task
One of the current application exports a file in the text file format to a specified directory. As soon as the file arrives in a specified directory the process should start which will import data from a text file to a SQL server table and then move that text file to a 'processed' directory. After the import part is completed successfully a report is generated in the text file format in a particular directory and then the report is mailed to muliple users.
I'm thinking of a DTS package for this functionality. But then I don't want to schedule the DTS package but the package should be executed only when a new file arrives in the specified directory.
Is it possible to execute a DTS package as a particular event occurs? Or is there another way to do this like using VBScript calling DTSRun etc?
Thanks for the help.
dtsrun
[/?] |
[
[
/[~]S server_name[\instance_name
{ {/[~]U user_name [/[~]P password]} | /E }
]
{
{/[~]N package_name }
| {/[~]G package_guid_string}
| {/[~]V package_version_guid_strin
}
[/[~]M package_password]
[/[~]F filename]
[/[~]R repository_database_name]
[/A global_variable_name:typei
[/L log_file_name]
[/W NT_event_log_completion_st
[/Z] [/!X] [/!D] [/!Y] [/!C]
]
[/?] |
[
[
/[~]S server_name[\instance_name
{ {/[~]U user_name [/[~]P password]} | /E }
]
{
{/[~]N package_name }
| {/[~]G package_guid_string}
| {/[~]V package_version_guid_strin
}
[/[~]M package_password]
[/[~]F filename]
[/[~]R repository_database_name]
[/A global_variable_name:typei
[/L log_file_name]
[/W NT_event_log_completion_st
[/Z] [/!X] [/!D] [/!Y] [/!C]
]
ASKER
Moving a file from one directory to another directory is not a problem. I'm looking for a solution to execute a DTS package only when a new file arrives in the specified directory.
Thanks
Thanks
what i said..
u need a 3rd party application to handle file copy opereation and trigger run dts event..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
yes arbert ur rite.. but mujoshi wants that dts must run when a file is copied... thats why i advice a 3rd party application
With Microsoft FTP you can send the FTP LOG to a database table..
All you need do then is setup a TRIGGER on that table to launch a stored procedure on a log entry ..
be carefull because not all entries are "file uploded" but it is quite easy to figure out...
then launch your stored procedure using DTS run launched from XP_cmdshell...
Jay
All you need do then is setup a TRIGGER on that table to launch a stored procedure on a log entry ..
be carefull because not all entries are "file uploded" but it is quite easy to figure out...
then launch your stored procedure using DTS run launched from XP_cmdshell...
Jay
alternatively you can create a simple VB app and run it as a service ..
Jay
Jay
ASKER
Thanks for your suggestion Jay. I have not worked with VB much but I'm trying this option. I'm giving 100 points to this solution.
Once again thanks everyone.
Once again thanks everyone.
Still not sure why anyone would code an extra application when the can accomplish the same thing from within SQL itself as a job.....
you have a point there arbert... probably easier that way .. keeps it all centralized
ASKER
Yes I know I can schedule a job to accomplish a task. But I was looking for a solution to execute a job only when a new file is loaded in to a specified folder. In other words I want to execute a DTS package only when a particular event occurs and don't want to a schedular to check when that event will occur.
Here I've given only one event but the project I'm working on might have multiple file events which will require diffrent actions to take.
Thanks
Here I've given only one event but the project I'm working on might have multiple file events which will require diffrent actions to take.
Thanks
You see that's exactly the problem ...
can you tell us a good reason why you don't want to do it the way we suggest? ..
if its a performance issue i can understand that ... which is why i recommended a vb application..
the basic problem is that you can't attach to a "DIRECTORY" insert event.
ive solved this before using the FTP way ...
or Directory scanning every so many minutes ..
anyone with a better idea? ...
the best case is that you create a "registration app" that Loads the file and places an entry into your table..
a Web page is great for this .. using the ability to upload a file with ASP or ASP.net
Jay
can you tell us a good reason why you don't want to do it the way we suggest? ..
if its a performance issue i can understand that ... which is why i recommended a vb application..
the basic problem is that you can't attach to a "DIRECTORY" insert event.
ive solved this before using the FTP way ...
or Directory scanning every so many minutes ..
anyone with a better idea? ...
the best case is that you create a "registration app" that Loads the file and places an entry into your table..
a Web page is great for this .. using the ability to upload a file with ASP or ASP.net
Jay
3rd Party is needed .. cuz i guess someone or an application uploads a text file to a spesific Folder and it dont ave a period. this text file is overwritten eveytime. thats why when the file is created we ave to handle this and execute the dts..
Melih SARICA
Melih SARICA
"3rd Party is needed .. cuz i guess someone or an application uploads a text file to a spesific Folder and it dont ave a period. this text file is overwritten eveytime. thats why when the file is created we ave to handle this and execute the dts.."
And what happens if the 3rd party software doesn't run like it's suppose to? The file would still get overwritten. That's why you usually use a date convention as part of the file name.
And what happens if the 3rd party software doesn't run like it's suppose to? The file would still get overwritten. That's why you usually use a date convention as part of the file name.
we ave an DTSRUn utility to run dts packs from dos prompt
Melih SARICA