March 21, 2006 at 9:44 am
I'm trying to create a dts package from a non microsoft db to sql server 2000 db. Everything is set but the issue is that on a normal insert update and delete... all this happens by triggers. The question is: can DTS call those triggers to place the information being transfered into those selected tables. I know you can transfer triggers and SP's with a package but can they be executed also.
THanks
March 21, 2006 at 10:00 am
So what is the DTS package transferring? Not records, I presume. Why do you want to call triggers when they look after themselves after inserts/updates etc?
You'll have to be a bit more expansive about what you're trying to do.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 21, 2006 at 4:23 pm
what I'm trying to is a data conversion from a non window's based db into an existing SQL DB... I truncated the whole SQL DB so all the tables are clear. The question is how does the SQL DB handle the DTS package when half the tables are normally filled by triggers and stored procs... Unless it doesn't matter when importing data verses inserting or apending data.
March 22, 2006 at 6:04 am
Yes it matters (see next para). The triggers will fire as usual, because they are defined at table level. So you need to avoid importing into those tables that will be populated by triggers and let SQL Server do that work for you. But there is no guarantee that the data in the SQL Server tables that have been populated by triggers will be the same as in the non-SQL Server db - eg date created, data last modified, modified by user fields will be different, for a start.
As for stored procedures, if you need these to run, you will have to specifiy within your DTS package exactly what is required. Obviously, this requires a thorough deep understanding of all of the stored procs - when they are used and what their arguments are.
Have you considered just importing all of the data directly? Obviously, you'd need to disable any triggers before performing the import, but that's not too difficult.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 22, 2006 at 9:00 am
interesting, the triggers still didn't fire... we just did a complete import of all tables.
March 24, 2006 at 4:49 am
What I did for one of my projects, was create a SP which is started from an insert trigger.
This SP then creates a new unique job using newid() as the job name (removing the - from the ID) with auto delete job after finishing the job.
It also adds a few steps to the job, one of which is a DTSRUN step. This step starts a DTS Package with a parameter (a table name) which is stored in a global variabe within the package when it's executed.
The last step of the SP is to start the job it just created.
I hope this helps
Cheers
R
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply