May 30, 2012 at 9:09 am
How do you convince someone that using Powershell in their environment would be a good thing?
Let's not go the "it's got all these nifty, gee-whiz-golly-cool features" route. Let's talk about a logical argument that can be presented to a manager in an office that does not use Powershell and sees no need for it because Powershell "doesn't do anything that can't be done with native SQL functionality."
I can see the point. Why use Powershell at all if it's just an extra maintenance headache? However, I'm positive there has to be something about Powershell that makes it so useful that everyone is talking about it. I just don't know what it is.
So please help me understand it so I can convince someone else that Powershell would be worth introducing in their environment.
May 30, 2012 at 10:10 am
I spend time trying to convince myself. One example I came across was using powershell to gather sizing information without recreating linked servers. I had earlier used linked servers and gathered the information using those, but using powershell I dropped the linked servers and still make the calls outside.
So part of the help is being able to centralize managment both of sql and windows type information.
Mark Johnson
MCP, MCTS Sql Server 2005,MCTS Sql Server 2008, OCP
May 30, 2012 at 10:36 am
In our shop we do most of our automated monitoring and admin tasks via powershell scripts, run from central admin instances.
This gives you far greater scalability in a large complex environment, as well as ease of management and maintenance of your automation code.
When we bring a new SQL server instance online, all we have to do is add a row to our server/instance table on the admin instance in the given environment and the instance is fully monitored and administered.
We even manage our Sybase and Oracle databases servers via poweshell scripts, executed via SQL agent jobs on our central admin instances.
May 30, 2012 at 10:43 am
Powershell can work outside of SQL Server and is less cumbersome than stringing scripts through SQLCMD. That provides a good way to handle administrative tasks, especially when they involve the file system.
I wouldn't necessarily use Powershell to do things in SQL Server unless these were things that I needed to do across multiple machines, and perhaps involved the file system.
Most of the MS products are including Powershell interfaces, and there are places where it is easier to connect to SQL and get things done. For example, I use a PoSH script to pull binary data out of a Filestream column and build the binary files (images). That's something that is more cumbersome and problematic with other tools.
It's worth learning so that you can use it when necessary, but don't force it into places where SQL Server has other tools that work well.
May 30, 2012 at 8:16 pm
Steve Jones - SSC Editor (5/30/2012)
Powershell can work outside of SQL Server and is less cumbersome than stringing scripts through SQLCMD. That provides a good way to handle administrative tasks, especially when they involve the file system.I wouldn't necessarily use Powershell to do things in SQL Server unless these were things that I needed to do across multiple machines, and perhaps involved the file system.
Most of the MS products are including Powershell interfaces, and there are places where it is easier to connect to SQL and get things done. For example, I use a PoSH script to pull binary data out of a Filestream column and build the binary files (images). That's something that is more cumbersome and problematic with other tools.
It's worth learning so that you can use it when necessary, but don't force it into places where SQL Server has other tools that work well.
+1. I completely agree. It's a tool. A very powerful tool. And like a great technical professional, it is useful to know and to use. It has it's place, and can save a lot of time and eliminate a lot of error. But just like other tools, there's a time and place for it. Using PowerShell for 100% of DBA tasks would be absurd. Likewise, brushing it off and not learning it and utilizing in the right places wouldn't be advised. PoSh is big and getting bigger.
May 31, 2012 at 12:06 pm
Is SSIS integrated with Powershell?
May 31, 2012 at 12:44 pm
Meaning can you call PoSH from SSIS? Or Vice Versa?
I think the confusion is that PoSH isn't much of anything. It's a shell for accessing components. In the SQL space, when someone talks about running a backup from PoSH, what they are really doing is running a backup from SMO (COM/.NET DDL objects) and using PoSH for the manipulation of the SMO objects.
Similar objects for Windows, Exchange, etc.
June 4, 2012 at 3:20 pm
Brandie Tarvin (5/31/2012)
Is SSIS integrated with Powershell?
You can use PowerShell from within SSIS using this[/url] (works well). Or you can call an SSIS package from PowerShell by invoking DTExec (no integration of app domains if done this way). If you need app domain integration from PowerShell you can do it but I have not known the need for such heavy integration in that direction. If you needed it though, since you can create new or manipulate and invoke existing SSIS packages in .NET it means you can do the same using PowerShell, see here for details.
Both SSIS and PowerShell are managed language environments built atop .NET. PowerShell is a scripting language layer built atop of .NET. If you're familiar with Perl you know it is a layer built atop C, same idea. Do you need to know .NET to use PowerShell, no, not at all. Same with SSIS. Do you need to know .NET to create an SSIS package, not at all, but it's right there if you need it in the form of Script Tasks and Script Components. You can create very powerful SSIS and PowerShell solutions without knowing one lick of .NET, but it's right there behind the scenes if you need it.
SMO are .NET assemblies so it is no accident that PowerShell scripts that leverage SMO are easy to develop. The existence and enhancement of the managed providers Steve referenced for other MS platforms (e.g. Windows, SharePoint, Exchange, obviously SQL Server) show that MS is backing the technology in a strategic way. PowerShell and Sql Server Central Management Server is also a very powerful combination.
From PowerShell we can conveniently access handy things like WMI, Web Services, Event Logs, T-SQL, AD...pretty much anything proprietary in a Microsoft environment as well as anything standard in a mixed environment (e.g. DNS, NNTP, SMTP, etc.). These services be accessed using generic .NET methods either directly using the .NET base-class libraries or from nicely-packaged built-in PowerShell CmdLets (e.g. Get-WmiObject). Can we manage multiple Windows servers running SQL Server from a T-SQL context, all from a single SQL Server? Sure, if we try hard enough, but it is not as easy as doing the same using PowerShell, I have done both. As a proponent I think anyone with more than a mere few instances to manage would be missing the boat if they ignored PowerShell for system and database administration.
PowerShell has been out for over 5 years and it is clear MS is backing the technology.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2012 at 6:37 am
I would like to be able to use Powershell from within SSIS to do things like file management (without having to have xp_cmdshell enabled). The sort of things that the File System Task can't do. So I guess I'm looking for an existing component that can use a PS script.
As far as the PS link that OPC.three gave me, I would love to use stuff like that. However, corporate procedures dictate that we can't use stuff like that, or the Trash Destination, or the Attunity connectors mentioned on The Thread unless those items have gone through rigorous testing and been approved by corporate. It makes sense. We're a big enough organization that having software conflicts in Production could cause some major issues. Especially if I can't vouch for the security of the software / object (such as "It won't steal our data").
So if I want a SSIS Trash Destination, I have to build the thing myself. Yes?
I'm assuming from your comments that none of the native SSIS tasks aside from the Script Task can actually take PS commands and that there's no Microsoft add-on (which would fall under the approved category) for SSIS that would have such tasks in it. Is that a correct assumption?
And I'm really tired this morning, so let me check this... Am I even making sense?
June 7, 2012 at 8:31 am
Brandie Tarvin (6/7/2012)
I would like to be able to use Powershell from within SSIS to do things like file management (without having to have xp_cmdshell enabled). The sort of things that the File System Task can't do. So I guess I'm looking for an existing component that can use a PS script.
The Execute Process Task is to SSIS what xp_cmdshell is to T-SQL. You can use it to get to a command prompt and issue PowerShell commands. The syntax could get quite nasty though given all the misdirection you'd be going through (Exec Process Task > Command Line > cmd.exe + /c > PowerShell commands).
As far as the PS link that OPC.three gave me, I would love to use stuff like that. However, corporate procedures dictate that we can't use stuff like that, or the Trash Destination, or the Attunity connectors mentioned on The Thread unless those items have gone through rigorous testing and been approved by corporate. It makes sense. We're a big enough organization that having software conflicts in Production could cause some major issues. Especially if I can't vouch for the security of the software / object (such as "It won't steal our data").
Totally understandable. It's been that way at most of the shops I have worked in, including the current one. The only comfort in the component I provided is it's open source so you can vet it and compile it yourself to be sure it's free of Easter Eggs.
So if I want a SSIS Trash Destination, I have to build the thing myself. Yes?
Well, I suppose you could send things to a RAW file destination that you never use again, but that's disk i/o. Or you can send things to a Script Component Destination to get the same effect, but that requires custom coding.
I'm assuming from your comments that none of the native SSIS tasks aside from the Script Task can actually take PS commands and that there's no Microsoft add-on (which would fall under the approved category) for SSIS that would have such tasks in it. Is that a correct assumption?
Same as above answer, you can use the Execute Process Task to issue PowerShell commands, no custom coding or components required but it could get nasty to manage that code. The difference between that and the custom task I provided a link to is the app domain integration. With the component I provided a link to you can pass variables in and out of the PowerShell script from SSIS and write PowerShell in a natural editor (no escaping or nasty misdirection to get it to run through the Execute Process Task). Also, the Execute Process Task would spawn a new thread and SSIS would just treat it as an external process, only seeing the return code.
And I'm really tired this morning, so let me check this... Am I even making sense?
Makes perfect sense. All of these are valid concerns and ones you should be exploring before drinking the Kool Aid.
If you want to do complex file management can you not do it all in PowerShell? Why involve SSIS in that task at all? SSIS for ETL, thumbs up. PowerShell for system and database admin work, also a thumbs up. Mixing the two may be wear it gets muddy. What types of things are you thinking you would need to do where you would want to blend the two so heavily?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2012 at 8:40 am
opc.three (6/7/2012)
If you want to do complex file management can you not do it all in PowerShell? Why involve SSIS in that task at all? SSIS for ETL, thumbs up. PowerShell for system and database admin work, also a thumbs up. Mixing the two may be wear it gets muddy. What types of things are you thinking you would need to do where you would want to blend the two so heavily?
Except that we have files that we get from vendors that we then have to process with ETL, then we need to do other things with the files, like rename them and send them on to someone else, after we process them. It's easier to manage it all in one process, rather than have multiple individual programs that do the ETL part and then do the file management part.
Granted, the FST can do the renaming part of the process, but some of the hoops we have to jump through, like reading directory contents and deleting multiple files without deleting all directory content or doing zipping / extracting of files, are a bit of a PITA. We're not using xp_cmdshell as much as we used to after our upgrade to 2008, but it's still the only solution my poor little brain has been able to come up with for some of our situations.
Powershell would be a nice alternative if I can figure out how to A) use it, B) apply it to our problems in such a way that it doesn't cause worse issues, and C) convince the bosses that it is worth our while to use it.
June 7, 2012 at 9:43 am
Brandie Tarvin (6/7/2012)
opc.three (6/7/2012)
If you want to do complex file management can you not do it all in PowerShell? Why involve SSIS in that task at all? SSIS for ETL, thumbs up. PowerShell for system and database admin work, also a thumbs up. Mixing the two may be wear it gets muddy. What types of things are you thinking you would need to do where you would want to blend the two so heavily?Except that we have files that we get from vendors that we then have to process with ETL, then we need to do other things with the files, like rename them and send them on to someone else, after we process them. It's easier to manage it all in one process, rather than have multiple individual programs that do the ETL part and then do the file management part.
Granted, the FST can do the renaming part of the process, but some of the hoops we have to jump through, like reading directory contents and deleting multiple files without deleting all directory content or doing zipping / extracting of files, are a bit of a PITA. We're not using xp_cmdshell as much as we used to after our upgrade to 2008, but it's still the only solution my poor little brain has been able to come up with for some of our situations.
I agree, some of the file system tasks you mentioned are a PITA to handle using SSIS built-in components. In reading your post multiple Foreach Loop Containers, Execute Process Tasks and File System Tasks came to mind, and lots of Variables and Expressions to construct as well. You are right to look at PowerShell as it could ease that burden significantly. I can think of scenarios where one PowerShell command would replace multiple FELCs containing multiple FSTs.
Powershell would be a nice alternative if I can figure out how to A) use it, B) apply it to our problems in such a way that it doesn't cause worse issues, and C) convince the bosses that it is worth our while to use it.
I am sure you'll knock out A & B if you decide it's worth your time. On C all I can do is wish you luck. I think PowerShell could help you here, but we know how trying to convince people to adopt new ideas can go 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply