Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Is it possible to execute a DTS package as a particular event occurs?

Posted on 2005-05-06
18
Medium Priority
?
367 Views
Last Modified: 2013-11-30
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.
0
Comment
Question by:mujoshi
  • 5
  • 4
  • 3
  • +1
15 Comments
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 13947778
u need an 3rd party application to handle the file copy process to that directory and then to run the DTS ..
we ave an DTSRUn utility to run dts packs from dos prompt

Melih SARICA


0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 13947785
dtsrun
[/?] |
[
    [
        /[~]S server_name[\instance_name]
        { {/[~]U user_name [/[~]P password]} | /E }
    ]
    {    
        {/[~]N package_name }
        | {/[~]G package_guid_string}
        | {/[~]V package_version_guid_string}
    }
    [/[~]M package_password]
    [/[~]F filename]
    [/[~]R repository_database_name]
    [/A global_variable_name:typeid=value]
    [/L log_file_name]
    [/W NT_event_log_completion_status]
    [/Z] [/!X] [/!D] [/!Y] [/!C]
]


0
 

Author Comment

by:mujoshi
ID: 13947832
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 19

Expert Comment

by:Melih SARICA
ID: 13947888

what i said..

u need a 3rd party application to handle file copy opereation and trigger run dts event..

0
 
LVL 34

Accepted Solution

by:
arbert earned 2000 total points
ID: 13951727
"u need a 3rd party application to handle file copy opereation and trigger run dts event.."

You can handle all of this from within DTS/SQL Server....

Basically, you just create a SQLAgent job that runs every Xminutes checking the directory for new files--it's not a real time event, but it works well.
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 13955569

yes arbert ur rite.. but mujoshi wants that dts must run when a file is copied... thats why i advice a 3rd party application



0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 13959290
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
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 13959299
alternatively you can create a simple VB app and run it as a service ..

Jay
0
 

Author Comment

by:mujoshi
ID: 13961953
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.
0
 
LVL 34

Expert Comment

by:arbert
ID: 13970451
Still not sure why anyone would code an extra application when the can accomplish the same thing from within SQL itself as a job.....
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 13970465
you have a point there arbert... probably easier that way .. keeps it all centralized
0
 

Author Comment

by:mujoshi
ID: 13970606
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
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 13970653
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
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 13975140
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
0
 
LVL 34

Expert Comment

by:arbert
ID: 14227437
"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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question