Execute a DTS package from a trigger?

[Craig Lister]

I have a requirement to import data from one SQL Server (2005)
database, to another, transforming the data on the way through. So,
DTS seems to fit that requirement perfectly.

My problem is, how to lanch that DTS package. Source database gets
populated, and then as soon as possible, we need to start importing
from that database. The data in the source is actually pumped in via a
3rd party. They are open to assisting us. I've propsed that they
create a 'ExportDetail' type table in the source database. Once their
import completes, the write the last ID of the master table table to
the ExportDetail table.

The master table would be last ID of the top most record. For example,
if we have a Customer->Orders->Products heirarchy, then CustomerId
would be the ID we're interested in. Once they complete their import,
when then grab from the Last ID back towards a locally stored
'FirstId'. Sorry, confusing.

We would know the lastId that we last imported. So we would then read
Customers from OUR last ID, to their last ID.

So, once they complete their import, they INSERT a row into the
ExportDetails table with the LastID. Now, there would be a trigger on
that table. An INSERT trigger. Could this trigger be used to set off a
DTS package? The package could then grab the LastID and store it on
our side, and then grab all the rows.

Does this seem like a good solution or is there better way to do this?

[Todd C]

Craig:
Couple of thoughts for you:

*Since you posted this in the "DTS" forum, and since you mention "DTS" then
I'll assume you are refering literally to "Database Transformation Services"
in SQL 2000. Yet you allude to SQL 2005. My suggestion is to design your
stuff in SQL Server Integration Services. DTS support *will* be going away
eventually. SSIS will have a longer usable life.

*Using a trigger to *directly* start another process may lead to trouble. If
that process fails, then the trigger fails (even if you have error handling
code in it like a TRY CATCH block). A better approach might be to go through
SQL Server Service Broker. SSSB is an asynchronous messaging system within a
database. In your case, it would work like this: The trigger executes a
stored procedure which puts a message in a queue. The trigger has now done
everything it needs and can complete the operation to which it is attached.
The message gets sent from the outbound queue to an inbound queue (within the
same database/server, or not). Another sp reads the queue and takes
appropriate action on it.

Good luck. Keep us posted.
--
Todd C
MCTS SQL Server 2005

Post by Craig Lister
I have a requirement to import data from one SQL Server (2005)
database, to another, transforming the data on the way through. So,
DTS seems to fit that requirement perfectly.
My problem is, how to lanch that DTS package. Source database gets
populated, and then as soon as possible, we need to start importing
from that database. The data in the source is actually pumped in via a
3rd party. They are open to assisting us. I've propsed that they
create a 'ExportDetail' type table in the source database. Once their
import completes, the write the last ID of the master table table to
the ExportDetail table.
The master table would be last ID of the top most record. For example,
if we have a Customer->Orders->Products heirarchy, then CustomerId
would be the ID we're interested in. Once they complete their import,
when then grab from the Last ID back towards a locally stored
'FirstId'. Sorry, confusing.
We would know the lastId that we last imported. So we would then read
Customers from OUR last ID, to their last ID.
So, once they complete their import, they INSERT a row into the
ExportDetails table with the LastID. Now, there would be a trigger on
that table. An INSERT trigger. Could this trigger be used to set off a
DTS package? The package could then grab the LastID and store it on
our side, and then grab all the rows.
Does this seem like a good solution or is there better way to do this?
.

[Russell Fields]

Craig,

If you are using DTS or SSIS, you can create a SQL Agent job to run that
package. Create a job with no schedule, then a trigger could be written to
issue the command to start the job. E.g.

EXEC msdb.dbo.sp_start_job N'Import Data Job'

If you do it this way, however, the job cannot pick up the LastID from the
trigger but would need to read it from the ExportDetails table. You should
also make sure that the logic ensures that all the unprocessed rows in
ExportDetails are processed. (If the 'Import Data Job' is running, an
attempt to start it again will fail.)

FWIW,
RLF

Post by Craig Lister
I have a requirement to import data from one SQL Server (2005)
database, to another, transforming the data on the way through. So,
DTS seems to fit that requirement perfectly.
My problem is, how to lanch that DTS package. Source database gets
populated, and then as soon as possible, we need to start importing
from that database. The data in the source is actually pumped in via a
3rd party. They are open to assisting us. I've propsed that they
create a 'ExportDetail' type table in the source database. Once their
import completes, the write the last ID of the master table table to
the ExportDetail table.
The master table would be last ID of the top most record. For example,
if we have a Customer->Orders->Products heirarchy, then CustomerId
would be the ID we're interested in. Once they complete their import,
when then grab from the Last ID back towards a locally stored
'FirstId'. Sorry, confusing.
We would know the lastId that we last imported. So we would then read
Customers from OUR last ID, to their last ID.
So, once they complete their import, they INSERT a row into the
ExportDetails table with the LastID. Now, there would be a trigger on
that table. An INSERT trigger. Could this trigger be used to set off a
DTS package? The package could then grab the LastID and store it on
our side, and then grab all the rows.
Does this seem like a good solution or is there better way to do this?

[Craig Lister]

Thanks very mich guys.

Todd, I am looking at that SSSB thing. I have never used it, but I
have google. :) That's for warning me about that issue. So, the
trigger would basically be 'stuck' with the SSIS package fires? Not
good. Thanks.

My mistake as well - we're using SQL Server 2005, and SSIS. I keep
forgetting that the name is different. I have updated my documentation
to reflect that. Thanks again.

Russell, thanks! My knowledge on the SSSB is limited, and if I battle,
your option of EXEC msdb.dbo.sp_start_job might work well. But, does
that lock up the trigger until the job completes?

Thanks guys.

[Russell Fields]

Craig,

EXEC sp_start_job only gives the command to start the job, so the trigger
will end once the job is started. The job itself runs asynchronously from
the trigger in another process.

RLF

Post by Craig Lister
Thanks very mich guys.
Todd, I am looking at that SSSB thing. I have never used it, but I
have google. :) That's for warning me about that issue. So, the
trigger would basically be 'stuck' with the SSIS package fires? Not
good. Thanks.
My mistake as well - we're using SQL Server 2005, and SSIS. I keep
forgetting that the name is different. I have updated my documentation
to reflect that. Thanks again.
Russell, thanks! My knowledge on the SSSB is limited, and if I battle,
your option of EXEC msdb.dbo.sp_start_job might work well. But, does
that lock up the trigger until the job completes?
Thanks guys.
.