November 8, 2006 at 7:17 am
I want my users to have the ability to fire off a DTS at will.
Senario is they get an extract, place it into a specified directory then click a button and the extract is processed into the server, all the queries and matches are made and the result in placed into a another specified directory.
My thinking is that a stored procedure will be needed to run the DTS but I haven't been able to find anything in writing.
Also, can a DTS call another DTS?
Jeff
November 8, 2006 at 8:37 am
I've seen lots of methods for executing a DTS package from a stored procedure or an ASP page. You can use xp_cmdshell & dtsrun, OLE stored procedures, or set up an unscheduled job to execute the package and use sp_start_job to run the job. I've used the last method successfully.
Search this site for "execute DTS from stored procedure" and check out http://www.sqldts.com/.
Yes, you can execute a package from another package with the Execute Package task.
Greg
Greg
November 8, 2006 at 8:38 am
Jeff
The dtsrun command is what you need for the first point. It's a command-line utility so you don't need to call it from a stored procedure - you can use a batch file or run it straight from your button. Check out the syntax in Books Online and don't forget to consider any security implications.
I'm sure there are ways of calling one DTS package from another. Again, dtsrun would work, but there may be a simpler way.
John
November 8, 2006 at 9:14 am
That web site is wonderful, thanks for the lead.
I run sql statement after the transformation process. During the processing of the sql statements, duplicate keys were ignored which makes the package return as failed. How do I stop that?
November 8, 2006 at 11:55 am
I'm not sure I understand this problem. Is there duplicate data in the input file that would result in duplicate key errors in the destination table?
Could you post the structures of your input file and destination table, some sample data, and a complete description of your DTS package?
Greg
Greg
November 8, 2006 at 12:01 pm
What I am doing is bouncing data from one extract against others. In SQL I have the intersections table where duplicate values, as a result of the matching, are ignored.
This results in the DTS thinking that there were errors in the execution of the package.
November 9, 2006 at 8:43 am
Please post the error that DTS is reporting. If you have package logging enabled, the error will be shown there.
Greg
Greg
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply