Creating and executing DTS package
-
Hello everyone, I have got stuck in a big mess. The situation demands creation of a DTS package based on a query in SQL server and then execute it to produce the output in a text file. My problem is that I am completely new to this DTS stuff, so can anyone please help..?? Here is the exact thing I want to do :- 1. Create a DTS package based on a query. 2. Execute the package using .NET. 3. On the completion of the DTS package execution, send a mail stating the success or failure of the DTS package execution. The mail should be .NET generated. Below are the things which I have tried to do on my own and my queries regarding the same :- 1. I have generated a package for a particular query, using SQL enterprise manager but dont know how to pass parameters to the package when executing it. 2. Is there any way to make the above package creation dynamic? I mean can we create a DTS package using .NET?
-
Hello everyone, I have got stuck in a big mess. The situation demands creation of a DTS package based on a query in SQL server and then execute it to produce the output in a text file. My problem is that I am completely new to this DTS stuff, so can anyone please help..?? Here is the exact thing I want to do :- 1. Create a DTS package based on a query. 2. Execute the package using .NET. 3. On the completion of the DTS package execution, send a mail stating the success or failure of the DTS package execution. The mail should be .NET generated. Below are the things which I have tried to do on my own and my queries regarding the same :- 1. I have generated a package for a particular query, using SQL enterprise manager but dont know how to pass parameters to the package when executing it. 2. Is there any way to make the above package creation dynamic? I mean can we create a DTS package using .NET?
If yuo are running in code already why do you need to make a DTS package? These are normally called from a scheduler or filewatcher to load/extract data. If you are in control of the situation (as it appears from your post) then why not just execute the query directly? However, it is possible to dynamically modofy DTS packages by using VBA code to modify job steps. I am not sure if you could create a new package directly from code, but I suspect you could as they are just database objects. BTW, do you mean DTS packages or are you using VS2005, where you use SSIS as this is a whole different kettle of fish
Bob Ashfield Consultants Ltd
-
If yuo are running in code already why do you need to make a DTS package? These are normally called from a scheduler or filewatcher to load/extract data. If you are in control of the situation (as it appears from your post) then why not just execute the query directly? However, it is possible to dynamically modofy DTS packages by using VBA code to modify job steps. I am not sure if you could create a new package directly from code, but I suspect you could as they are just database objects. BTW, do you mean DTS packages or are you using VS2005, where you use SSIS as this is a whole different kettle of fish
Bob Ashfield Consultants Ltd
Thanks for the reply Bob, Yes I am talking about the DTS packages and not about SSIS. So far creating and executing of the package is concerned, I am able to do that now. For the time being I can do away with creating packages manually like I have already explained and then execute it through a stored procedure. The third step is still a problem for me, i.e. send email stating the success or the failure of the package execution. Although SQL server gives us the option to send the success/failure mail but the my mail contents are dynamic and depends on some parameters coming from my .NET application. So is there a way to signal the ASP .NET application about the DTS package execution completion? I hope I am clear with my words.
-
Thanks for the reply Bob, Yes I am talking about the DTS packages and not about SSIS. So far creating and executing of the package is concerned, I am able to do that now. For the time being I can do away with creating packages manually like I have already explained and then execute it through a stored procedure. The third step is still a problem for me, i.e. send email stating the success or the failure of the package execution. Although SQL server gives us the option to send the success/failure mail but the my mail contents are dynamic and depends on some parameters coming from my .NET application. So is there a way to signal the ASP .NET application about the DTS package execution completion? I hope I am clear with my words.
-
Sorry, its so long since I did any of this I can't remember the exact details, but I am sure you can make your DTS package return a value to indicate sucess/failure. Probably not much help I'm afraid.
Bob Ashfield Consultants Ltd