August 6, 2010 at 10:39 am
Our company has a DTS job that takes a data file and seperates it into seperate files of data for each user responsible for that data. Currently to send that file to each user we have seperate tasks for each user that checks for the existence of their file and if its found it emails it to them.
For whatever reason people leave and join our company all the time which makes maintaining this DTS package a pain. I was curious if there is a way to setup an Active X Script to use say another file of users to determine which files to check for and as it finds the file it can send it in an email to the user, thus requiring only one Active X script and one send mail task.
Please let me know if you need more information or anything else thank you.
August 8, 2010 at 10:43 am
michael.morse (8/6/2010)
Our company has a DTS job that takes a data file and seperates it into seperate files of data for each user responsible for that data. Currently to send that file to each user we have seperate tasks for each user that checks for the existence of their file and if its found it emails it to them.For whatever reason people leave and join our company all the time which makes maintaining this DTS package a pain. I was curious if there is a way to setup an Active X Script to use say another file of users to determine which files to check for and as it finds the file it can send it in an email to the user, thus requiring only one Active X script and one send mail task.
There is too little detail to tell but I think it is doable.
Having said that, why don't you replace such DTS package for a storedproc where you have absolute control of what is going on?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 8, 2010 at 11:58 am
Sounds like you need a table of tasks, and email address of person assigned to each task. Then the package just utilizes this table to send emails out. All you will need to do is to maintain this table, not the package.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 9, 2010 at 6:19 am
Okay the table of tasks sounds like what I am trying to accomplish. How would I go about implementing this?
September 3, 2010 at 11:41 am
michael.morse (8/9/2010)
Okay the table of tasks sounds like what I am trying to accomplish. How would I go about implementing this?
Michael, there are many places you could start, but you haven't given much useful information. For example, are there any Exec SQL steps in the package? If so, you might be able to re-use some of it in your new procedure. What's your server's mail situation? Do you have all the permissions/roles required to do this yourself? Will someone else be doing it? If the answer to that last question is "yes", then the very first place you need to start is to get buy in from that person's boss that he/she will do what you need. With out that, you are wasting your time. Just my two cents.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply