August 18, 2011 at 2:55 pm
@Gus,
Dang... I knew there was something else I wanted to ask and forgot it for a moment. If it's not obvious that I'm no SSIS-Ninja, let me say that I'm no SSIS-Ninja. With that thought in mind, rumor has it that a single SSIS package can't be executed concurrently with a 2nd instance of the same package running. Is that true?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2011 at 2:59 pm
Why thank you Jeff!
It is amazing how fast and what a person can learn about SQL, technology and business in general when in the near worst case scenario most of the time.
Someone once suggested I teach at the college level for technology. I would be the prof everyone hates, lol.
Real world problems.
"Ok, write this reporting app, based off the data in this db. Oh, small point, the db is presently an Omnidex "database" on a MPE/Unix system. Half the data is the result of a conversion up from Excel 95 solutions the finance group came up with. The other half is a 3rd party vendor solution, which has pretty weak validation of data, and no referential integrity. Oh, almost forgot, here is this green bar report of data which needs to be worked into the report"
A mutiny might ensue...
August 18, 2011 at 4:05 pm
Jeff Moden (8/18/2011)
@Gus,Dang... I knew there was something else I wanted to ask and forgot it for a moment. If it's not obvious that I'm no SSIS-Ninja, let me say that I'm no SSIS-Ninja. With that thought in mind, rumor has it that a single SSIS package can't be executed concurrently with a 2nd instance of the same package running. Is that true?
Not exactly... but..... you need xp_cmdshell to trigger independent dtexec's... :hehe:
The other method is sp_start_job, in which case this is correct.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 18, 2011 at 5:05 pm
David Lester (8/18/2011)
Yes, but I am nearly in the victory position now. This week ends the contract with the 4th DBA consultant they hired to come in to prove that the way I use SQL, and the standards I am fighting for are wrong, and theirs are correct.Why the 4th? The first three agreed with me, and echoed every suggestion I have given over the last several years. The 4th? He agrees with my ideas as well. Now we all made a promise that whatever this last one says is what will become the standard.
(Ok, I presume they will ignore the recommendations and continue on with their disasterous methods.)
Too funny David... They obviously didn't screen their DBA candidates closely enough ahead of time to ensure they were going to support their agenda!!! 😀 😛 I had to endure similar scrutiny at one employer, but their budget only allowed them to burn up two consultants before they gave up the fight! 😎
As for xp_CmdShell I've definitely been one to steer clear of it as much as possible. thanks everyone for the posts - been interesting to hear everyone's perspective on things.
Cheers!
August 18, 2011 at 6:13 pm
OzYbOi d(-_-)b (8/18/2011)
As for xp_CmdShell I've definitely been one to steer clear of it as much as possible. thanks everyone for the posts - been interesting to hear everyone's perspective on things.
Heh... oh no! I'm not going to let you get away that easily. 😀 Why is it that you "steer clear of it as much as possible"?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2011 at 6:15 pm
Evil Kraig F (8/18/2011)
Not exactly... but..... you need xp_cmdshell to trigger independent dtexec's... :hehe:
I have just three words for that very ironic statement...
BWAAAAA-HAAAA-HAAAAAAAAAAAAAA! 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2011 at 6:18 pm
David Lester (8/18/2011)
Real world problems."Ok, write this reporting app, based off the data in this db. Oh, small point, the db is presently an Omnidex "database" on a MPE/Unix system. Half the data is the result of a conversion up from Excel 95 solutions the finance group came up with. The other half is a 3rd party vendor solution, which has pretty weak validation of data, and no referential integrity. Oh, almost forgot, here is this green bar report of data which needs to be worked into the report"
BWAAA-HAAA-HAAA!!! Stop it! You're killing me! :-):-D:-P
What's really ironic is that I just went through something so close to that, you'd think it was the same person giving the requirements :hehe:.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2011 at 6:29 pm
Jeff Moden (8/18/2011)
Evil Kraig F (8/18/2011)
Not exactly... but..... you need xp_cmdshell to trigger independent dtexec's... :hehe:I have just three words for that very ironic statement...
BWAAAAA-HAAAA-HAAAAAAAAAAAAAA! 😀
I thought you'd enjoy that. :satisfied:
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 19, 2011 at 6:38 am
Jeff Moden (8/18/2011)
@Gus,Dang... I knew there was something else I wanted to ask and forgot it for a moment. If it's not obvious that I'm no SSIS-Ninja, let me say that I'm no SSIS-Ninja. With that thought in mind, rumor has it that a single SSIS package can't be executed concurrently with a 2nd instance of the same package running. Is that true?
Nope.
I just tested it. I created a simple SSIS package, deployed one copy of it to a test server, scheduled two jobs to run it at the same time, and it worked.
You can't run the same job twice at the same time, regardless of whether it's SSIS or anything else, so that's probably what you're thinking of.
- 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
August 19, 2011 at 7:22 am
GSquared (8/19/2011)
Jeff Moden (8/18/2011)
@Gus,Dang... I knew there was something else I wanted to ask and forgot it for a moment. If it's not obvious that I'm no SSIS-Ninja, let me say that I'm no SSIS-Ninja. With that thought in mind, rumor has it that a single SSIS package can't be executed concurrently with a 2nd instance of the same package running. Is that true?
Nope.
I just tested it. I created a simple SSIS package, deployed one copy of it to a test server, scheduled two jobs to run it at the same time, and it worked.
You can't run the same job twice at the same time, regardless of whether it's SSIS or anything else, so that's probably what you're thinking of.
Absolutely true from the scheduler viewpoint.
The only other trick that might have sparked your fancy like that is - you can create a package which can execute other packages. Meaning you could have a single package instantiating parallel runs of child packages (after you've given them different startup parameters).
So it's in theory possible to create the "mother of all SSIS packages", which can spawn 1,2, ...,n runs of a sub-package based on parameters passed into the parent on startup. It's still not the universal "you can run multiple instances of a SSIS DTSX package", but it's also not an outright no, either.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 19, 2011 at 8:16 am
Marios Philippopoulos (8/18/2011)
...
Sproc code is more easily accessible than a CLR assembly, even if the production server has been highly secured.
Databases are usually restored from prod backups on QA and dev environments, and those are generally non-secure environments, accessed by a lot of users.
CLR code is contained within a dll, and, although there are ways to hack into that, it is not as simple as getting to a plain-text sproc on a DEV/QA server.
Why do you thing CLR cource is less available in dev environments than sproc source? If sprocs are to go into production with non-admin users unable to see source, any development tests should be set up so that non-admin users can't see that source. People who have access to the source of sprocs presumably are those who have to develop/maintain/debug them, and people with access to the source of CLR routines presumably are those who have to develop/maintain/debug them - in what way is one different from the other?
Why on earth would sproc source be available (other than to someone with sa privilege) in a QA environment? - the only two reasons I can think of are (i) that the people responsible for taking that decision haven't a clue about security or (ii) the people with access to the QA machines are trusted very much (after all, they are trusted to release only stuff that won't wreck the production systems). Why would a production database be restored from a DEV server (instead of from a QA server)? The only two reasons are the same as for the last question.
Marios Philippopoulos (8/17/2011)One reason I can think of why, to me, CLR would be preferable to T-SQL: it introduces an extra layer of complexity to developing and deploying code as an assembly (say with EXTERNAL_ACCESS permissions), as opposed to a T-SQL module.
So an extra layer of complexity is a good thing? With its consequences (more development cost, extra opportunity to introduce obscure bugs)?
CLR code within an assembly is not as "visible" or "hackable" as code in a T-SQL sproc, and, therefore not as easy to break through and manipulate.
It is just as "visible" and just as "hackable", and anyway properly written code won't be broken or manipulated to serve an attacker's purpose just because it's visible (although invisibility does help protect badly written code from attack). If there is a difference in visibility, I have to ask why on earth you want to give users without admin privileges access to any part of the schema, let alone code (sproc) source.
There are things that I would use CLR for now that it is available. Other things I will want to do direct in cmdExec, so xp_cmdshell is the obvious solution (and provided access is properly restricted it causes no security issues that don't exist without it).
Tom
August 19, 2011 at 8:33 am
Matt Miller (#4) (8/19/2011)
GSquared (8/19/2011)
Jeff Moden (8/18/2011)
@Gus,Dang... I knew there was something else I wanted to ask and forgot it for a moment. If it's not obvious that I'm no SSIS-Ninja, let me say that I'm no SSIS-Ninja. With that thought in mind, rumor has it that a single SSIS package can't be executed concurrently with a 2nd instance of the same package running. Is that true?
Nope.
I just tested it. I created a simple SSIS package, deployed one copy of it to a test server, scheduled two jobs to run it at the same time, and it worked.
You can't run the same job twice at the same time, regardless of whether it's SSIS or anything else, so that's probably what you're thinking of.
Absolutely true from the scheduler viewpoint.
The only other trick that might have sparked your fancy like that is - you can create a package which can execute other packages. Meaning you could have a single package instantiating parallel runs of child packages (after you've given them different startup parameters).
So it's in theory possible to create the "mother of all SSIS packages", which can spawn 1,2, ...,n runs of a sub-package based on parameters passed into the parent on startup. It's still not the universal "you can run multiple instances of a SSIS DTSX package", but it's also not an outright no, either.
Please don't remind me of that!
I recently had to rebuild an SSIS package that had 6 child packages, each of which used multiple child packages, some of which also called child packages. Was four levels deep in places. All to export, encrypt, and FTP three text files. Debugging was horrific. (It's one package with three data flows in it now. Much easier to work with.)
- 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
August 19, 2011 at 10:34 am
GSquared (8/19/2011)
Please don't remind me of that!
I recently had to rebuild an SSIS package that had 6 child packages, each of which used multiple child packages, some of which also called child packages. Was four levels deep in places. All to export, encrypt, and FTP three text files. Debugging was horrific. (It's one package with three data flows in it now. Much easier to work with.)
Sorry for the pain! It certainly gets reminiscent of "death by views" (views referencing other views which reference other views, ad nauseam). As in - it's possible, but may not be desirable, especially not once you've extended it past 1 level of indirection.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 19, 2011 at 11:49 am
GSquared (8/19/2011)
Jeff Moden (8/18/2011)
@Gus,Dang... I knew there was something else I wanted to ask and forgot it for a moment. If it's not obvious that I'm no SSIS-Ninja, let me say that I'm no SSIS-Ninja. With that thought in mind, rumor has it that a single SSIS package can't be executed concurrently with a 2nd instance of the same package running. Is that true?
Nope.
I just tested it. I created a simple SSIS package, deployed one copy of it to a test server, scheduled two jobs to run it at the same time, and it worked.
You can't run the same job twice at the same time, regardless of whether it's SSIS or anything else, so that's probably what you're thinking of.
Thanks for the info, Gus. Guess I'm going to have to get with the SSIS folks and try that.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2015 at 11:49 am
Was looking into how to implement a secure way to use XP_CMDSHELL... I've read many of the posts... Very interesting...
Now! I'm more confused then when I started reading! LOL
Please write this article... I'm sure that many need it, me being one of them!!!
Great thread!
Viewing 15 posts - 46 through 60 (of 93 total)
You must be logged in to reply to this topic. Login to reply