April 23, 2011 at 2:51 pm
Steve Jones - SSC Editor (4/23/2011)
As a general rule, I don't allow it, especially for non-sysadmins.
As a general rule, I agree and I don't normally allow humans , regardless or whether they're an admin or not, to have ad hoc access to xp_CmdShell, either. As you and others have pointed out, there are way too many things to worry about just at the DB Server level never mind at the OS level. In fact, with rare exception, I won't use it on any public facing computer.
So where do I actually use it (not including exceptions)... high performance ETL "bricks".
Now... if I could only talk MS into bringing sp_MakeWebTask back, I wouldn't have a need for SSRS either. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2011 at 3:38 pm
No worries about anything adversarial, you're not. As long as you forgive me the lack of tone in text, we're good. 😉
Jeff Moden (4/23/2011)
Files with fixed width fields are no more difficult with BCP/BULK INSERT than they are with SSIS. You only need to define them as you would in SSIS. You have to somehow assign the starting position and the length of the fields. In SSIS, you use a GUI. With BCP/BULK INSERT, you use SUBSTRING.
Output my friend, OUTPUT! 🙂 In that case though you're going to cast as CHAR(). Still reads like a brick to me.
I don't work with SSIS so forgive my ignorance of the subject. Are you saying that SSIS does that automatically or do you have to tell it to do so? If I put foreign keys on my staging table (and I ALWAYS use a staging table), then BCP/BULK INSERT will put such rows in the errata file and a "control" file which contains the diagnostics for why each row failed. From BOL...
It does and doesn't. You build off your checks and then redirect the stream on failures. There are 'errors' but they're practically useless garbage, which I assumed was about the equivalent of what BCP would provide. It is incredibly easy however to put in a human usable custom failure in for each failure point. I could shortcut it to do it like BCP does and it's an all in one failure list against the constraints already built into the table, but that would require a handy-dandy translation of the esoteric trash that SSIS coughed up generically. (Everything isn't roses, no matter how handy it is. 😉 ) This however gives me the option on common failure causes.
That reminds me of some of the contests on some of the forums. Sometimes (a lot of times, actually), making more than one pass at the data is actually faster than trying to do everything to each row all at once. I'll be happy to agree that "It Depends". 😀
Oh, you know you're not getting an argument from me on that one. I might do a lot in the stream, but some things just don't make sense unless you staging dump and use the engine. Even minor 3 table joins are usually better off outside the tool.
I've not found a good one so I don't have a URL to post. Maybe I should write an article about it someday. 🙂
I have succeeded in my mission today! :w00t:
??? :blink: Gosh, Craig. What makes you think that converting a VARCHAR datetime to a DATETIME datatype is a problem for either BCP or BULK INSERT? It happens rather automatically for both.
As usual, I type too fast and make too many assumptions that ppl are in my head. It's so crowded in here I forget you're not. 😛 I was personally thinking translating crappy data, like 11-12-11 type stuff, that causes all sorts of silly depending on local settings and output methods from foreign sources. If 20111211 doesn't go in right we're all in a world of hurt. 🙂
So far as swapping a business entry for a local surrogate key goes, that's easily accomplished using a calculated column.
Hm. Get back to you on that...
Now I know how Adam Machanic feels when someone offers the same reasons for not allowing CLR's on their boxes. 🙂 The Lead on each Dev Team and the DBA's should be a closely knit team of their own and the Lead's should be doing the code reviews under the tutilage of the DBA. Further, considering the awesome and frequently unchecked power of SSIS packages, why would any DBA feel more comfortable with SSIS packages that may never reviewed by a DBA?
Because you can lock the SQL Agent who's running the stuff from having access to the WMI commands and the like that provide that power. It's another reason dedicated SSIS servers are becoming more common (besides that they can be memory hogs used poorly), so that administrators and the like can full-power the local maintenance agents, while restricting the SSIS agent to only needed abilities that specifically can't get their grubby little mitts on that 'awesome power'. Though, that's not very different from your proxy server, this proxy doesn't get xp_cmdshell but can still do what it needs to.
Regarding closely knit teams... Yeaaaah. Personal example: I currently have *2* DBAs I can work with out of 6 in a mixed environment. One is new to us who isn't allowed the entirety of production yet, and he's in the DBA team. The other one supports 15 dev teams (I think, hard to get exact numbers because of the orgcharts) while doing full prod support. In another state.
That's not uncommon, that's par for the course. I *am* tight with the DBAs, I *am* trusted, they know I'm competent, and if i want xp_cmdshell, I'm writing a four page explanation for the reasons behind it and why any workaround will not perform what's necessary... and I don't disagree with them.
I can certainly understand and live with that! It all boils down to this... the OP asked the question of "is SSIS worth it?" Your answer is a flat "Yes" and my answer is, as usual, "It Depends." 🙂
LOL, well, honestly, I didn't get a "It depends" from your first post, so bear with me. I do use SSIS for simpler data push tasks that don't require the hoops and loops available in SSIS that any of the tools could do with ease, but that's primarily for consistency and due to familiarity. I find it quicker to whip up a quick data push via SSIS GUI then writing out the code, as well, but again, that's familiarity. SSIS also incorporates things like the Bulk Import process and the like so I don't lose the power of those tools.
Your answers though are showing me it may come down more to familiarity and ease of use then anything to do with the individual items themselves. In this case, I have one tool that's rather visual so I find them very easy to troubleshoot and customize. You have multiple tools that require more access, but perform the same task.
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
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply