June 17, 2015 at 6:37 pm
My team is deciding to go with code-only solutions rather than continue our heavy reliance on SSIS. One limitation is that xp_cmdshell will not be enabled, so looping through files could be a bit complicated. However, since i've discovered undocumented stored procedures such as xp_dirtree, i've found that I can loop through file types in a specific directory and use that to bulk insert multiple files into a staging table. We think this works great and will be a great way to proceed.
One question I have is this - for how long will xp_dirtree be available in SQL Server? Does anyone know if future versions will contain it?
Probably an impossible question to ask, but thought i'd check and see if anyone has any insight.
Thanks!!
June 17, 2015 at 7:39 pm
Polymorphist (6/17/2015)
My team is deciding to go with code-only solutions rather than continue our heavy reliance on SSIS.
At the risk of sounding like a certain grumpy expert, why??? When you say "code", do you mean T-SQL? If so, you do realize that you picked one of the hardest languages to to do this type of thing in?
SQL is a data manipulation language, not a procedural programming language.
This task is much better suited for C#, VB, or any number of other languages.
What is the point of disabling is xp_cmdshell? If you need to use it, enable it.
Enough of being grumpy.
xp_dirtree is undocumented. MS will likely not announce that this would be something that may be deprecated. If it goes away, it will be a surprise! Use this at your own risk.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 17, 2015 at 10:07 pm
There is no guarantee that even something well documented will be in the next release, never mind undocumented features. Still, that doesn't stop me from using their power until they are actually removed and that's when I use a new feature (undocumented or otherwise) or an old feature that many frown upon.
That feature, of course, is xp_CmdShell and you're depriving yourselves of some very simple and elegant solutions by not enabling it.
Ah, but horrors!!! It's a major security problem, right? Others will differ with me but, IMHO, xp_CmdShell hasn't been a security problem since 2005 came out. Consider who can use it... unless you've made the awful security problem mistake of giving non-systemadmin users privs to run it directly, only sysadmins can turn it on or off and only sysadmins or the system itself can use it. To wit, xp_CmdShell isn't a security problem. Only bad security is a security problem. And, if the bad guys get in with sysadmin privs, having it turned off and even enforced by some profile won't prevent anything because a sysadmin user can use PowerShell or an CMDEXEC task to override just about anything. In fact, an attacker, internal or otherwise, that breaks in with sysadmin privs can even make a self deleting job that will steal the crown jewels of your company and leave nary a trace of what has happened because they're going to get in as one of you folks. 😉
Consider doing an audit of who or what has sysadmin privs, properly controlling the privs, protecting the server like you should, and then turn on xp_CmdShell so that you can take full advantage of the riches that live there.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2015 at 12:55 am
Thanks for the feedback.
Regarding SSIS, I have yet to work in any environment that really seriously uses it. I worked at a major company in the travel industry and they flat-out told me NOT to use it. Everything was done using T-SQL and Powershell. Since then i've really only met a very small handful of people who really like using SSIS. The company i'm at know has used it quite extensively, but that was mainly because the staff they had didn't really understand scripting languages such as Powershell. When i've used SSIS it was mainly for ingesting multiple flat-files into a table, from there I went the T-SQL route. With Powershell and these undocumented procedures, I can get around using SSIS. But the real reason we are not going to use it going forward is because it was a major hassle to open packages and chase-down tasks to edit or update. We have packages calling packages and it seemed as if it never ended. Now, don't get me wrong, I understand that's a result of how it was used and not the fault of SSIS itself, but we all still prefer code rather than SSIS. Also, it's the manager's idea so we're doing it 🙂
Regarding xp_cmdshell, it's simply not going to be enabled. Trust me, i've tried and i've fought that battle - it ain't gonna happen. I've been in companies that have it enabled and some that refuse to do it, I can only state my case and that's that. So for now, i'm stuck with the undocumented procedures and Powershell.
June 18, 2015 at 7:25 am
Understood on the xp_CmdShell thing. I was trying to give you fuel for your fire but there's a lot of overwhelming FUD out there and some companies just flat refuse to use it. As you imply, though, there are bigger things to bang your head on the wall for.
If you're ever interested, here's a link to a fairly recent MS whitepaper on security where even Microsoft says to use it if you need it. 😉
Also, congrats on being able to avoid SSIS. I do the same and my boss hates it so I'm in good company. And, you're right... there are some people that really know how to use it but, like you, it's pretty easy to avoid for the type of ETL we do at work (mostly high volume CSV/TSV or spreadsheets).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply