July 26, 2011 at 3:01 am
You can setup one-way transactional replication.
Also I don't see how running an SSIS packages is any more secure..
July 26, 2011 at 3:09 am
steveb. (7/26/2011)
You can setup one-way transactional replication.Also I don't see how running an SSIS packages is any more secure..
this is really ridiculous, i specifically mentioned above that the only way I have is through an SSIS trigger...didnt you read my post carefully. I cannot go against IT support dept. decisions. I dont have any other option and I mentioned that clearly..Either you can do is give a relavant answer or at least not block this post from replied to by others.
July 26, 2011 at 3:14 am
pwalter83 (7/26/2011)
steveb. (7/26/2011)
You can setup one-way transactional replication.Also I don't see how running an SSIS packages is any more secure..
this is really ridiculous, i specifically mentioned above that the only way I have is through an SSIS trigger...didnt you read my post carefully. I cannot go against IT support dept. decisions. I dont have any other option and I mentioned that clearly..Either you can do is give a relavant answer or at least not block this post from replied to by others.
I was simply pointing out that the information that the IT dept are basing their decisions on are not neccesarily correct and they may have forced you into a course of action that is not really neccesary.
Anyway not really that worried and will leave it there.
July 26, 2011 at 7:53 am
Elliott Whitlow (7/25/2011)
Hey G, how about cross-server service broker queues?CEWII
It would be worth looking into, but he said they have a security policy that only SSIS can modify data on the receiving server. I'm not sure if SSIS can access data in a service broker queue or not.
It's a dumb and ineffective security policy, but it's not the first time I've run into it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 26, 2011 at 8:10 am
pwalter83 (7/26/2011)
steveb. (7/26/2011)
You can setup one-way transactional replication.Also I don't see how running an SSIS packages is any more secure..
this is really ridiculous, i specifically mentioned above that the only way I have is through an SSIS trigger...didnt you read my post carefully. I cannot go against IT support dept. decisions. I dont have any other option and I mentioned that clearly..Either you can do is give a relavant answer or at least not block this post from replied to by others.
He's just trying to help. We understand you're desperate at this point, but we're all just trying to help.
Your IT dept is manned by ignorant morons, and that's not your fault. They don't know what they're doing, they set arbitrary rules that have no connection to reality at all and just make their ignorance more visible. We definitely feel for you on that. It's gotta suck. He's trying to be helpful, and hoping that you might convince your IT team to pull their heads out of .... which I'm sure you're probably tired of trying to do. Don't let it get to you.
The SSIS package will have to perform a delta check on the data it's moving. The easiest way to do that will be to insert the data into a staging table from the trigger, using the "inserted" and "deleted" tables, and then start the job using sp_start_job, and have the SSIS package use the staging table as it's source. If you flag which rows are additions, which are deletions, and which are updates, you can then perform the necessary actions on each.
So, the trigger would look something like this:
create trigger MyITDeptSucks on MyTable
for insert, update, delete
as
set nocount on;
insert into MyStagingTable (column list)
select column list,
case when deleted.ID is not nul and inserted.ID is not null then 'U'
when deleted.ID is not null then 'D'
else 'I'
as RowAction
from inserted
full outer join deleted
on inserted.ID = deleted.ID;
exec msdb..sp_start_job 'MyJobName';
The job will run an SSIS package that will have four steps. It will insert new rows, update the rows with "U" in the RowAction column, and delete the deleted rows, then it will clean out the staging table for the next run.
Keep in mind that if the job is already running, nobody will be able to update/insert/delete data in the table(s) the trigger exists on. They'll get an error (as I pointed out earlier) and their actions will fail. Your IT dept is too incompetent and stupid to realize this, so make sure you document it to CYA when people start complaining about the errors and managers start firing people because of it.
There's no way to avoid those errors within the constraints you've been given, as I already pointed out, so you're pretty much screwed at this point on job security, unless your IT department can be replaced with a higher grade of drunken chimpanzees.
You could add a Try Catch routine to the trigger that would abort if the job is already running, but that'll defeat the whole purpose of what you're trying to do, so it doesn't solve the problem either.
So, set up the SSIS package, set up the job, create the trigger, and start job hunting. Make the job hunting the top priority, because you're going to be better off jumping ship than hanging around in a place that employs people like that and gives them the authority to make any decisions at all, ever.
I'm not kidding about the job hunting part.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 26, 2011 at 9:42 am
GSquared (7/26/2011)
pwalter83 (7/26/2011)
steveb. (7/26/2011)
You can setup one-way transactional replication.Also I don't see how running an SSIS packages is any more secure..
this is really ridiculous, i specifically mentioned above that the only way I have is through an SSIS trigger...didnt you read my post carefully. I cannot go against IT support dept. decisions. I dont have any other option and I mentioned that clearly..Either you can do is give a relavant answer or at least not block this post from replied to by others.
He's just trying to help. We understand you're desperate at this point, but we're all just trying to help.
Your IT dept is manned by ignorant morons, and that's not your fault. They don't know what they're doing, they set arbitrary rules that have no connection to reality at all and just make their ignorance more visible. We definitely feel for you on that. It's gotta suck. He's trying to be helpful, and hoping that you might convince your IT team to pull their heads out of .... which I'm sure you're probably tired of trying to do. Don't let it get to you.
The SSIS package will have to perform a delta check on the data it's moving. The easiest way to do that will be to insert the data into a staging table from the trigger, using the "inserted" and "deleted" tables, and then start the job using sp_start_job, and have the SSIS package use the staging table as it's source. If you flag which rows are additions, which are deletions, and which are updates, you can then perform the necessary actions on each.
So, the trigger would look something like this:
create trigger MyITDeptSucks on MyTablefor insert, update, delete
as
set nocount on;
insert into MyStagingTable (column list)
select column list,
case when deleted.ID is not nul and inserted.ID is not null then 'U'
when deleted.ID is not null then 'D'
else 'I'
as RowAction
from inserted
full outer join deleted
on inserted.ID = deleted.ID;
exec msdb..sp_start_job 'MyJobName';
The job will run an SSIS package that will have four steps. It will insert new rows, update the rows with "U" in the RowAction column, and delete the deleted rows, then it will clean out the staging table for the next run.
Keep in mind that if the job is already running, nobody will be able to update/insert/delete data in the table(s) the trigger exists on. They'll get an error (as I pointed out earlier) and their actions will fail. Your IT dept is too incompetent and stupid to realize this, so make sure you document it to CYA when people start complaining about the errors and managers start firing people because of it.
There's no way to avoid those errors within the constraints you've been given, as I already pointed out, so you're pretty much screwed at this point on job security, unless your IT department can be replaced with a higher grade of drunken chimpanzees.
You could add a Try Catch routine to the trigger that would abort if the job is already running, but that'll defeat the whole purpose of what you're trying to do, so it doesn't solve the problem either.
So, set up the SSIS package, set up the job, create the trigger, and start job hunting. Make the job hunting the top priority, because you're going to be better off jumping ship than hanging around in a place that employs people like that and gives them the authority to make any decisions at all, ever.
I'm not kidding about the job hunting part.
Thanks GSquared, but I did confirm that only a single person would be updating the table and he would wait till the job finishes running.
I also know that you and other people on this forum are only trying to help but the IT restrictions coupled with my limited experience in SQL has made me to just jump out of the window. I wouldnt lose my job because of this as this scenario has been explained to everyone and even the IT staff tends to agree with it but they have their own limitations which they only know and besides I work for a shipping organization which only has 4 people in the development team and out of which only 2 work full time in development including me.
What I need at this point of time through is to create an SSIS package which should work based on the insert, delete or update command in trigger. I have got some idea on what you have suggested but it still is a long way to go for me. Anyway thanks for your effort and time.
July 26, 2011 at 10:00 am
SSIS can't access the data inside the trigger. Nothing except the trigger can do that. That's why you'll need to stage the data into a table that SSIS can access.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 26, 2011 at 10:05 am
GSquared (7/26/2011)
SSIS can't access the data inside the trigger. Nothing except the trigger can do that. That's why you'll need to stage the data into a table that SSIS can access.
I am not able to understand what you have mentioned, this is way out of my league....staging data into a table and the sample trigger that you have created above are very much new to me....i am lost...guess i wud have to stick with SSRS and search for a new job...
July 26, 2011 at 10:19 am
pwalter83 (7/26/2011)
GSquared (7/26/2011)
SSIS can't access the data inside the trigger. Nothing except the trigger can do that. That's why you'll need to stage the data into a table that SSIS can access.I am not able to understand what you have mentioned, this is way out of my league....staging data into a table and the sample trigger that you have created above are very much new to me....i am lost...guess i wud have to stick with SSRS and search for a new job...
I'm going to try to break this down a little further for you, but this is really a recap of what GSquared suggested.
It sounds like you get the concept of a trigger. G provided you with some sample code that would let you use a trigger - the one named MyITDeptSucks, in his sample code - to grab the events as they hit your first table and put them into a staging table.
Think of a staging table as a temporary drop point - like leaving a package on a colleague's desk for them to look at later. Trigger #1 (aka MyITDeptSucks) grabs data as it hits your first table, and drops it into a place and in a format where it can be more easily used.
That's just the first part. The second part would be grabbing that now nicely organized data from the staging table (pick up the box that got left on your desk) and sending it over to your secondary server. If you can sacrifice complete real time, you could do this with a scheduled package that runs *really* frequently (pick the interval of your choice) - or your could write a trigger on the staging table that then calls your SSIS package and uses the data from the staging table itself (eg "grab everything I haven't flagged as already processes and send it to that destination over there.) Given the limitations you are dealing with, that would probably be handled by Trigger #2 - one that still needs to be written.
Does the above make sense?
It's still going to have some potential pitfalls, but given the limitations you are dealing with, you're going to have pitfalls no matter what.
So - that's one option for a design concept.
Now I'm going to ask a potentially stupid question. It sounds like you're an SSRS designer, and you don't deal with this side of SQL much. Do you have the option of going to your boss and saying something like "This is a part of SQL that I don't generally handle and requires a different skill set. Can I bring in a consultant, or do you want me to spend the time to learn how to do this?" It might take some educating to teach your boss about the very different skill sets involved in report writing vs. database administration - but would it ultimately help you get to a resolution that doesn't have you feeling lost and stuck?
-Ki
July 28, 2011 at 5:11 am
Kiara (7/26/2011)
pwalter83 (7/26/2011)
GSquared (7/26/2011)
SSIS can't access the data inside the trigger. Nothing except the trigger can do that. That's why you'll need to stage the data into a table that SSIS can access.I am not able to understand what you have mentioned, this is way out of my league....staging data into a table and the sample trigger that you have created above are very much new to me....i am lost...guess i wud have to stick with SSRS and search for a new job...
I'm going to try to break this down a little further for you, but this is really a recap of what GSquared suggested.
It sounds like you get the concept of a trigger. G provided you with some sample code that would let you use a trigger - the one named MyITDeptSucks, in his sample code - to grab the events as they hit your first table and put them into a staging table.
Think of a staging table as a temporary drop point - like leaving a package on a colleague's desk for them to look at later. Trigger #1 (aka MyITDeptSucks) grabs data as it hits your first table, and drops it into a place and in a format where it can be more easily used.
That's just the first part. The second part would be grabbing that now nicely organized data from the staging table (pick up the box that got left on your desk) and sending it over to your secondary server. If you can sacrifice complete real time, you could do this with a scheduled package that runs *really* frequently (pick the interval of your choice) - or your could write a trigger on the staging table that then calls your SSIS package and uses the data from the staging table itself (eg "grab everything I haven't flagged as already processes and send it to that destination over there.) Given the limitations you are dealing with, that would probably be handled by Trigger #2 - one that still needs to be written.
Does the above make sense?
It's still going to have some potential pitfalls, but given the limitations you are dealing with, you're going to have pitfalls no matter what.
So - that's one option for a design concept.
Now I'm going to ask a potentially stupid question. It sounds like you're an SSRS designer, and you don't deal with this side of SQL much. Do you have the option of going to your boss and saying something like "This is a part of SQL that I don't generally handle and requires a different skill set. Can I bring in a consultant, or do you want me to spend the time to learn how to do this?" It might take some educating to teach your boss about the very different skill sets involved in report writing vs. database administration - but would it ultimately help you get to a resolution that doesn't have you feeling lost and stuck?
Thanks a lot everyone for your help and suggestions. Resolved this through VB script.
July 28, 2011 at 5:44 am
pwalter83 (7/28/2011)
Thanks a lot everyone for your help and suggestions. Resolved this through VB script.
Would you mind going into a little more detail about your solution? I'm sure there are others who could benefit from it!
-Ki
July 28, 2011 at 5:50 am
How about just recording key data with a trigger to a staging table, then have a sqlagent job launch your SSIS job through an alert ?
Have a look at http://www.sqlservercentral.com/scripts/Miscellaneous/31032/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 12 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply